Post reply

American Dates when DB language is British

  • June 3, 2003 at 2:07 am

    #80344

    I don't know if anyone else has had this problem in SQL2000 with SP 3, but I keep getting out-of-range error messages when using the getdate() function. The problem is, the result of the function is in US, even when the DB's language is British. I've tried formating the date and setting the language to british in code, but still no joy. The main offender is Query Analyzer. Is there a hidden language setting somewhere?

  • June 3, 2003 at 2:16 am

    #459473

    Hi richiey,

    maybe this thread will help you

    http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=12271&FORUM_ID=23&CAT_ID=2&Topic_Title=date+problem+with+SQL+2000&Forum_Title=General

    Might be the link is splitted

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 4, 2003 at 2:20 am

    #459657

    In Enterprise Manager

    • right click on the server
    • On the resulting dialogue box choose the tab "Server Settings"
    • The first option shows the server language, choose "British English"

    In Query Analyser

    • From the tools menu choose options
    • On the resulting dialogue box choose the tab Connections
    • 2nd from bottom check box is user regional settings

    Dates and times are a pain in SQL Server, particularly if you have to deliver to external customers. I tend to stick with old fashioned CONVERT statements to allow for such problems.

    LinkedIn Profile

  • June 4, 2003 at 8:01 am

    #459732

    Use

    SET DATEFORMAT 'dmy'

    or similar.

    Works for me

  • June 4, 2003 at 2:21 pm

    #459823

    These problems normally occurs when the 'date' is stored as a string / varchar and then compare with a date variable/value. If all the variables are datetime and you assign getdate() to it there should be no problem. Force the string to date/time with a convert function and use the "SET DATEFORMAT 'dmy'" as mentioned by gbn.

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

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