What is the best format for date literals to avoid SQL Server language setting problems?

Posted on 2005-04-29
Last Modified: 2008-02-01
SQL Server 2000.

I am trying to fix a bug with a SQL statement where a date literal is rejected by SQL Server. The literal is in the form 'yyyy-mm-dd', e.g. INVOICE_DATE<='2005/4/29'.

This is fine when the server language is "English" but fails when the language is "British English", with the error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

What I would like to know is if there is a safe date literal format in SQL Server that will be accepted regardless of language setting? For example I changed the above statement to INVOICE_DATE<='2005/APR/29' which was accepted, but I don't know if this would work with French or non-English language settings.
Question by:purplesoup
    LVL 10

    Expert Comment

    Hi purplesoup,
    > INVOICE_DATE<='20050429' should always work

    LVL 1

    Expert Comment

    Use convert

    CONVERT(VARCHAR(10), INVOICE_DATE, 101)  <= '04/29/2005'

    you can use any format by changing the third parameter (currently 101)
    LVL 1

    Expert Comment

    Please try this....
    CONVERT(VARCHAR(10), INVOICE_DATE, 101)  <= '2005-04-29'

    Author Comment

    Could you say why - is this from experience or is it documented somewhere?
    LVL 10

    Accepted Solution


    have a look at the more info section here;en-us;173907

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now