Hi Guys
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?
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"]
In almost all cases, the correct way to solve this is simply to never treat the date as a string. If you pass in a parameter, or use the (typed) column value, then the server's text conversion simply isn't a factor. In addition to avoiding the i18n issue, this also reduces your injection attack surface. And it saves a few CPU cycles, too ;-p
If you are using EXEC for dynamic SQL, then this should likewise be parameterised via sp_ExecuteSQL.
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
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.
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'