Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    33

    Unanswered: Strange problem with Dates

    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?

    Any insight into this problem is appreciated.

    Thanks,

    David
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    i suggest that u check whether a "SET DATEFORMAT" command is issued or not in the application

    in any connection, if you issue a "SET DATEFORMAT ydm" followed by the type of WHERE (Date >= 'yyyy-mm-dd') that u r using, it will produce different results.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  4. #4
    Join Date
    Apr 2006
    Posts
    33
    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.

    Thanks again,

    David
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  5. #5
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •