CODEWITHSUNDEEP
sql-servert-sql
user2261741
user2261741

Reputation: 61

CAST failing to create date

I'm getting a cast failure and I can't fathom why.

Here's what is failing : 

select cast('16/04/2012' as datetime)

The error is :

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

If I use CONVERT with a 103 for format it works without issue as you would expect.

The server is set to british date format, therefore the MSSQL account should also be defaulting to britsh format. It's been rebooted, so it shouldn't be that the service is using a different date format.

This is a SQL2005 instance.

What I really want to know is, what could be causing the CAST to fail?

Upvotes: 0

Views: 203

Answers (6)

Iwade Rock
Iwade Rock

Reputation: 174

Always use a language neutral date format - it will always work regardless of any settings:

CAST('YYYYMMDD', AS DATE);

4 digit year, 2 digit month, 2 digit day and NO separators. You will never again have to worry about default language settings.

Upvotes: 0

Geoff
Geoff

Reputation: 8850

Are you sure the server is British language format? If I run:

set language british
select cast('16/04/2012' as datetime)

Then I get:

2012-04-16 00:00:00.000

You can check the current session language with 

select @@language

The session language defaults from the login in use, assuming it's a SQL Server-provisioned login (i.e. not a Windows user). To check the language for a given user:

select loginproperty('myuser', 'DefaultLanguage')

To make a permanent server change for all newly created logins:

EXEC sp_configure 'default language', 23
reconfigure

...where 23 is the langid obtained via sp_helplanguage.

Upvotes: 0

dferidarov
dferidarov

Reputation: 602

Use this code:

SELECT convert(datetime, '16/04/2012',105)

Upvotes: 1

Alex
Alex

Reputation: 8937

You have a data format as MM:dd:YY, and cast tries to convert you '16/04/2012' which is 'dd/MM/yy' and throws exception because 16 is less then 12 monthes.

You can either change your data format in server settings or use SET DATEFORMAT statement before your query

Upvotes: 0

David R.
David R.

Reputation: 280

Date format and datetime format are not necessarily the same. While it may implicitly add the 00:00:00 for hh:mm:ss, maybe try adding that. Type 103 only includes dd/mm/yyyy, so it of course works.

Upvotes: 0

paul
paul

Reputation: 22001

Have you tried SET DATEFORMAT DMY?

Upvotes: 0

Related Questions