November 9, 2015 at 8:19 am
Hello all,
I always assumed and was also led to believe that date format yyyy-mm-dd is the safest way to ensure any kind of date related comparisons when it comes to SQL server's default language setup in US English or British English format.
But it appears that I am wrong.
I have a server SQL server 2008 R2.
Server Properties shows English (United States)
Default language is: English
Security Login default language is: English.
Under the above settings a SQL like this works fine:
select contactID,
[name],
b.started,
b.finished,
case
when started < '2015-11-01' and finished >= '2015-11-01' then 1
when started < '2015-11-01' and finished is null then 1
when started is null and finished is null then 1
end as 'October15'
....
If I change my Security Login default language to British English, the above SQL will not give the desired output of "1" for October15.
In any of the SQL scripts there is no "set dateformat" clause.
Why would the behaviour of SQL differ when it comes to dates, just by changing Security Login's default language, even though using "safe" yyyy-mm-dd format?
Many thanks in advance.
Vinay
November 9, 2015 at 8:29 am
There are 2 formats which are dateformat and language settings independent.
Date only: YYYYMMDD
Date and time: yyyy-mm-ddThh:mi:ss.mmm
That means that your format is not safe. I know it seems inconsistent, but it still complies with the ISO 8601 standard.
November 9, 2015 at 8:30 am
I guess your first clause should also be like this:
case
when started < '2015-11-01' and finished >= '2015-10-01' then 1
November 9, 2015 at 8:42 am
Thank you Luis.
The "finished" date is correct. it indicates that if the event started before Nov 1st but finished upto Nov 1st, then we count that "event" in October month.
If I remove all the dashes in yyyy-mm-dd and replace with yyyymmdd, then is it safe regardless of the login default language setting?
November 9, 2015 at 8:45 am
datsun (11/9/2015)
If I remove all the dashes in yyyy-mm-dd and replace with yyyymmdd, then is it safe regardless of the login default language setting?
Yes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply