Post reply

Changing default date for database

  • June 27, 2005 at 10:14 am

    #164992

    Hi

    I have a web application which sends a date value to my SQL Server database in the form of DD/MM/YYYY. Unfortunately my SQL Server database won't allow this and produces an error message on the page. I have been trying to code it so that it sends it in the format MM/DD/YYYY, but so far without success.

    I was wondering is it possible to change the default setting for the date on my database so that it will accept DD/MM/YYYY.

    Any help is greatly appreciated.

    Thanks

    Brendan

    P.S.

    Was wondering if the individual database could have its dates setting changed and not the SQL Server setting for all databases held.

  • June 27, 2005 at 10:20 am

    #568678

    Can you send the data in the format 'yyyy-mm-dd'?  This format should work regardless of American/English date worries.

    Regards

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 27, 2005 at 10:33 am

    #568686

    Thanks for the quick reply. Will try to code your solution, just thought it might be easier to change a setting on the database.

    Sending dates from web apps to databases seems to take up most of my coding time!

  • June 27, 2005 at 12:55 pm

    #568730

    SET DATEFORMAT is what you're looking for--read the (sparse) details in BOL. This lets you change the date format "used" by the current connection. There's probably a server-wide date format setting somewhere, but I'd recommend leaving that kind of setting to the "out of the box" default values.

    What you want to do is have your front-end application use a "date" value (datatype), rather than use a string value. (Keeps the user from entering Feb 31, 2005, or 13/13/05.) Whether this is easy or hard depends on the front-end language you're using.

    My number one piece of SQL Server character-to-date translation adivise: use "MMM DD YYYY hh:mm:ss.sss" format (example: Jun 26 2005 13:36.30.333 -- you can safely drop the time stuff.) SQL can always translate this into a datetime value. Probably not too useful for your application, alas.

       Philip

     

  • June 28, 2005 at 3:09 am

    #568854

    Can you change the default language to British English, in Server Properties?

    Sharing knowledge saves valuable time!
    Simon Martin

  • June 28, 2005 at 3:42 am

    #568860

    Hi

    Thanks again for all your help. Have taken Philips advice and coded so that the date format sent to the database is of the format MMM DD YYYY. All seems to be working.

    Cheers.

    Brendan

  • June 28, 2005 at 9:58 am

    #569010

    Just for information:

    SET DATEFORMAT YDM

    SELECT CAST( '2005-05-07' AS DATETIME)

    SET DATEFORMAT YMD

    SELECT CAST( '2005-05-07' AS DATETIME)

    Produces WRONG result. I.e you can't trust the format 'YYYY-MM-DD'.

    Though going with the ISO 8601 produces the right result:

    SET DATEFORMAT YDM

    SELECT CAST( '2005-05-07T00:00:00' AS DATETIME)

    SET DATEFORMAT YMD

    SELECT CAST( '2005-05-07T00:00:00' AS DATETIME)

  • June 28, 2005 at 10:09 am

    #569019

    Yup, I was talking twaddle once again.

    Best go home and swat up

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 8 posts - 1 through 7 (of 7 total)

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