Datetime issue with stored procedure in SQL Server

Question

The following code doesn't appear to run when executed by code:

Stored procedure snippet:

 AND (@month_ref = 81201 
      AND tsks.grouping_ref = @grouping_ref 
      AND ts.start_dtm BETWEEN convert(datetime, '2011-11-28') 
                           AND convert(datetime,'2012-01-01')
     )
  OR (@month_ref = 81202 
      AND tsks.grouping_ref = @grouping_ref 
      AND ts.start_dtm BETWEEN convert(datetime,'2012-01-02') 
                           AND convert(datetime,'2012-01-29')
     )...

Server setup:

SQL Server is setup as:

Language: English (United States)

And Windows Server region and language are set to:

English (United States)

Our test server is set up for British English and the stored procedure appears to work just fine. I have a feeling the customer setup doesn't like the dates.

DB design / data

ts.start_dtm looks like this in the database:

datatype - datetime
```
2011-04-01 00:00:00.000
```

So its setup as - YEAR | MONTH | DATE

My stored procedure is hardcoding the start_dtm as '2012-01-02' which is also in the same format.

Can you help me please?

The problem is the website fails when it tries to run the stored Procedure. The issue was the data conversion. I had to assign the format of date conversion.

You haven't actually said what error/problem you're getting - could you update the question to add this? — Ed Harper
Are you saying the british setup works? set language british; select cast('2011-11-28' as datetime) should fail, us_english should succeed — Alex K.
Actually, the YYYY-MM-DD format does NOT work in SQL Server - e.g. with "British English". You'll need to use YYYYMMDD instead (no dashes!) to be truly language-independent. — marc_s

Guffa Guffa · Accepted Answer · 2012-03-01T10:48:12

You shouldn't need an explicit conversion on the dates at all. This is ISO8601 format, so the database should understand it straight off:

ts.start_dtm Between '2011-11-28' AND '2012-01-01'

If you need to convert them, specify what format you are using for the strings:

ts.start_dtm Between convert(datetime,'2011-11-28', 120) AND convert(datetime,'2012-01-01', 120)

Datetime issue with stored procedure in SQL Server

1 Answers