[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-04-29
5
Medium Priority
?
308 Views
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.
0
Comment
Question by:purplesoup
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:lengreen
ID: 13892971
Hi purplesoup,
> INVOICE_DATE<='20050429' should always work


Cheers!
0
 
LVL 1

Expert Comment

by:Mannaraprayil
ID: 13893031
Use convert

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

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

Expert Comment

by:Mannaraprayil
ID: 13893049
Please try this....
CONVERT(VARCHAR(10), INVOICE_DATE, 101)  <= '2005-04-29'
0
 

Author Comment

by:purplesoup
ID: 13893088
Could you say why - is this from experience or is it documented somewhere?
0
 
LVL 10

Accepted Solution

by:
lengreen earned 1000 total points
ID: 13893103
purplesoup,

have a look at the more info section here

http://support.microsoft.com/default.aspx?scid=kb;en-us;173907
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

873 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