Date Conversion Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date Conversion Problem

Author

Topic

neostar
Starting Member

18 Posts

Posted - 2005-02-25 : 03:19:23

I must admit, one of the grey areas I have with SQL is dates. Can someone explain to me why I get an error with the following code

Declare @StartDate DateTime
Set @StartDate = '25/10/2004'
SELECT * FROM E_INRCTFIL
WHERE E_INRCIDAT = CONVERT(datetime, @StartDate, 103)

I get the following error
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Yet if I run this code, everything runs great

SELECT * FROM E_INRCTFIL
WHERE E_INRCIDAT = CONVERT(datetime, '25/10/2004', 103)

What is wrong with my variable decloration?

Thanks

Dan

RM
Yak Posting Veteran

65 Posts

Posted - 2005-02-25 : 04:11:04

It is validating the date format of your server before setting it for the variable @StartDate. If your server settings are mm-dd-yyyy then try this code.

Declare @StartDate DateTime
Set @StartDate = '10/25/2004'
SELECT * FROM E_INRCTFIL
WHERE E_INRCIDAT = CONVERT(datetime, @StartDate, 103)

neostar
Starting Member

18 Posts

Posted - 2005-02-25 : 04:16:04

Thanks that worked great!

Is the SQL server set up wrong though? I live in the UK so our date format is 25/10/2004.

Thanks again

Dan

neostar
Starting Member

18 Posts

Posted - 2005-02-25 : 05:22:05

Found a nice little statement on the MSDN knowledgebase which works a treat

SET DATEFORMAT dmy

Thanks

Dan

RM
Yak Posting Veteran

65 Posts

Posted - 2005-02-25 : 05:26:34

BOL say that SS2K can be installed with many languages defined on the server. The definitions for each language deteremines how date data is interpreted

For more details search "dates" and then "formats" in BOL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources