Post reply

SQL Server date format : ''day'' is treated as ''month''

  • August 23, 2007 at 8:58 am

    #180476

    Hi,
     
    There is an web based application which when submitting a query to the backend SQL Server, with date in the format of dd/mm/yyyy, the same is being interpreted as mm/dd/yyyy. The SQL Server throws an data type conversion error whenever the 'dd' part exceeds 12 as it is interpreted as invalid 'month' by the server (all the dd, mm and yyyy parts are supplied from the front end and concatenated as dd/mm/yyyy before being submitted to the server). Even the Query Analyzer throws the same error. I have changed the date format to 'dmy' (was 'mdy' previously) and the query did work through query analyzer...but the application still fails.
     
    Strangely, the production version, with a date format set to 'mdy' runs without a single issue.
     
     
    Any idea please?

  • August 23, 2007 at 9:59 am

    #729024

    Your servers probably have different national language setting.  Probably one is US English and the other is British English.  You can look at the value of @@language.

     

    To prevent these types of errors, always send character date strings to SQL Server in format YYYYMMDD.  This format is the only unambiguous format for SQL Server.

    Example for date only: 20070823

    Example for date and time: 20070823 23:44:55.997

     

     

  • August 23, 2007 at 10:56 am

    #729041

    Thanks a lot. Will try and update.

     

     

  • August 23, 2007 at 10:56 pm

    #729173

    Thanks for your suggestion Michael,

    For the current issue, I have checked both the servers, @@language yields 'us-english' in both.

    Any thoughts please ?

  • August 23, 2007 at 11:58 pm

    #729180

    Yes... Stop passing the date as a VARCHAR and pass it as a date/time data type or a date serial number.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply