Post reply

Date formats

  • September 23, 2003 at 7:20 am

    #82962

    I have installed a new sever but when i run a query to select where date = '20/09/2003' it errors but

    select where date = '09/20/2003'.

    The regional settings seem to be ok any tips on why. also everything i have read says regional settings fror date formats are set by the client this does not seem to be true as these quesries work on all sql severs except for this one.

    Any infomation please.

  • September 23, 2003 at 7:39 am

    #475408

    Can you check which time zone is your new server in compated to other servers?

    Just a thought!!!

    .

  • September 23, 2003 at 7:49 am

    #475410

    they are all in GMT london.

  • September 23, 2003 at 8:57 am

    #475438

    Are all users set to the right language / date formats on the servers? eg are the users set to "British English" on one server, but "English" (which defaults to US) on the other?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • September 23, 2003 at 9:20 am

    #475446

    I think that is the problem other servers default is british englich wheras this server they are english. i transferred these logins using the dts task seems strange it changed the language default.does anyone know a query wich will update all my logins rather than me changing through EM?

    thanks thomas

  • September 23, 2003 at 12:08 pm

    #475475

    Use this in ALL your queries:

    SET DATEFORMAT dmy

    -SQLBill

  • September 24, 2003 at 1:33 am

    #475540

    check the default language of the database users.

    From the treeview of "sql Server Enterprise Manager" go to Microsoft sql server\Sql server group\"Server Name"\Security\users.

    select the user linked to the database and with the right button of the mouse go to properties and check the default language

  • September 24, 2003 at 2:29 am

    #475550

    Why not just use explicit dates in the format of '20/Sep/2003' this will get around any differences in the regional settings....

  • September 24, 2003 at 7:34 pm

    #475709

    I agree with sismith, and that is the format I use in all my queries. Never has a problem.

  • September 24, 2003 at 10:23 pm

    #475714

    It is a good idea to query date fields using yyyy/mm/dd format to get the correct result. for eg. if your regiional setting is mm/dd/yyyy and date is stored in the same format, when you query, give the date in yyyy/mm/dd format.

    good luck

  • September 25, 2003 at 1:35 am

    #475730

    If you put the date in the format yyyymmdd i.e. no separators between the various bits of the date, then SQL Server will correctly interpret the date regardless of regional settings.

    Using yyyy/mm/dd (i.e. with separators), is interpreted as yyyy/dd/mm in some regions.

Viewing 11 posts - 1 through 10 (of 10 total)

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