Different countries specify dates in different formats - I live in the UK where the format is dd/mm/yy. In other countries the format is mm/dd/yy which can cause some confusion. Is 01/04/2006 the first of April or the 4th of January?
To avoid this ambiguity, I usually write dates in the format yyyy-mm-dd - as far as I know there is only one way to interoperate the date. 2006-04-01 would always be the 1st of April rather than the 4th of January. Or so I thought...
A user was running a query using SPSS (Statistics program) that connects to our SQL Server database via an ODBC link. They modified the query to include the following where condition:
where att_date >= '2006-01-01' and att_date < '2006-04-01'
The rows returned only included Jan-01 to Jan-03. I thought SPSS might be modifying the query and converting the date format to an ambiguous format - I run a Profiler trace and this is not the case. I copied the query from profiler into query analyzer and the query returned the 3 months worth of data as expected.
If the user runs the query from Management Studio Express on their computer they only get 3 days worth of data. If I logon to their computer and run the same query I get 3 months data as expected. This seems to suggest a problem with the user profile - how could this happen though? There is a work around for the user: to use the convert function - I thought this wasn't needed if the date format is specified as yyyy-mm-dd?
While I've only seen this very rarely, and then only in the UK (which of course can affect you), the problem lies in the Windows setting for Short Date formatting. I'd bet that the particular user having problems has set their format to yyyy-dd-mm instead of the UK's traditional DD/MM/yyyy setting.
Thanks. The user noticed the problem when using SPSS, but the problem also occurs when using Management Studio. I got the user to test on another machine - same problem. I logged on to that machine and it worked ok for
me. It just appears to be a problem with this one user...
I don't suppose you know where to change the date format? I thought yyyy-dd-mm wasn't a valid date format in any country. I'll run a profiler trace to check for the set dateformat option.
I had a similar problem for particular users and found it was to do with their (roaming) profile - this was on a Citrix server.
In the Registry under HKEY_CURRENT_USER, go to 'Control Panel' > 'International' and you will see all the settings that apply to that user in terms of locale, date formats, time formats etc.
You will probably need to change sLongDate and sShortDate.