SQL Server Datetime issues. American vs. British?
Solution 1
You can use SET LANGUAGE to choose the date format that SQL Server expects in queries (I think management studio uses client computer's regional settings for display purposes, not sure though). However, I suggest passing values using parameters instead of embedding them in query statement. You won't encounter any issues if you use parameters. Everything is taken care of.
set language us_english
declare @d datetime = '1929/12/18'
set language british
declare @d datetime = '1929/12/18' -- fails
To change the server default language:
declare @langid int = (select langid from syslanguages where name = 'british')
exec sp_configure 'default language', @langid
reconfigure with override
Solution 2
Personally, I always use YYYY-MM-DD format (or YYYYMMDD) since it's not culture-specific, and, well, I guess it appeals to me because it's "logical" (especially when followed by a time).
[Edit: I'm just talking about what I put in my SQL scripts to ensure compatibility regardless of the server settings, not what SQL Server "displays"]
Solution 3
You can set the default language for each indvidual SQL Server login. Can't quite remember, but something like this:
sp_defaultlanguage @loginame = 'LoginName', @language = 'Language'
Solution 4
If you pass in DATETIME in the format
dd MMM yyyy
for example
"11 JUL 2009"
there is never any ambiguity around month and date and therefore you should never have a problem
Solution 5
I try to use the ODBC canonical form of a date wherever possible {d 'yyyy-mm-dd'} This way I know how sql server will interpret it. It works in TSQL just fine.
super9
I work as a developer for a startup in Singapore. Messing around with iOS in my spare time.
Updated on June 27, 2020Comments
-
super9 almost 4 years
On my test DB, the dates are displayed in a DD/MM/YYYY format. By displayed I mean when you right click, open table in Management Studio, the returned data are displayed in a DD/MM/YYYY format.
Funny thing is, when I write T-SQL to retrieve records, I have to input a MM/DD/YYYY format to get back the right data. Is there anyway I can align this to a DD/MM/YYYY format?
-
super9 almost 15 yearsHow does one go about changing the T-SQL Datime query defaults to this?
-
super9 almost 15 yearsThis is a great but I am looking for a permanent solution rather than a session solution.
-
super9 almost 15 yearsThe dates are acutally stored as DATETIME formats. Sorry if I wasn't clear.
-
mmx almost 15 yearsNai: syslanguages alone will work if you are in master database. Adding
sys.
to that line made the scrollbars appear so I shortened it ;) -
Marc Gravell almost 15 yearsI assumed that; my point is that it shouldn't matter how the server wants to handle them as text if you simply never (in your system) treat them as text.
-
super9 almost 15 yearsOh right I get what you mean. I normally pass them in as variables but good advice nonetheless. Thanks for that!