Error on CONVERT(Datetime,'2000-01-01')

Hello,

We have several stored procedure which convert literal strings to date in the format off: CONVERT(Datetime,'2000-01-01') or such.
Those procedures are working well on our development server as well as on several other servers we installed at our customers.
However, on an MSSQL server that was install in Germany, we get an error on those convert line. The error the MSSQL return is: "Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert au?erhalb des gültigen Bereichs." which more or less translate too:
"During the conversion of a char data type into a DATE time data type lies DATE-TIME-WORTH outside of the valid range."

I managed to manually fix this by changing the convert line to the following: CONVERT(Datetime,'20000101') , but I still have few questions:

1. I would be happy to know what is the reason for the inconsistent behavior of the CONVERT(Datetime,'2000-01-01') and if there is a way to solve it without changing the stored procedure (maybe configuring something in the MSSQL setting or regional setting of the server itself).

2. Can I be assured that the CONVERT(Datetime,'20000101') will work properly on each and every instance of MSSQL, or can this format might also fail on different machines.

Thanks in advance.

Run this in Query Analyzer

SELECT CONVERT(Datetime,'2000-12-13')

SET DATEFORMAT dmy
SELECT CONVERT(Datetime,'2000-12-13')

The second one will fail because SQL server expects a date in yyyyddmm format, un Europe they use the ddmmyyyy format

Lookup SET DATEFORMAT in BOL

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Forgot to answer you question ;-(

YYYYMMDD (no dashes) is the safe ISO dateformat to use

Denis the SQL Menace

http://sqlservercode.blogspot.com/