DATETIME Query problem

Question

DATETIME Query problem

Cookie SQL

Right there with Babe

Points: 715
More actions
January 9, 2012 at 4:52 am

#262668

When developers are running cast on one of the systems the day and month are transposed.
eg
SELECT DATEPART(mm, CAST('2012-09-01 05:20:02.917' AS DATETIME))
returns 1
However these two variations work as expected:
SELECT DATEPART(mm, CAST('20120901 05:20:02.917' AS DATETIME))
returns 9
SELECT DATEPART(mm, CAST('2012-09-01 05:20:02.917' AS DATE))
returns 9
anybody any ideas what i am missing here.
SQL 2008 10.0.2531
The regional settings look identical as does the default language

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

When converting strings to date & time types, use the CONVERT function instead of CAST; it allows you to be specific about the format (style):

SELECT

CONVERT(datetime, '2012-09-01 05:20:02.917', 121),

DATEPART(MONTH, CONVERT(datetime, '2012-09-01 05:20:02.917', 121));

See http://msdn.microsoft.com/en-us/library/ms187928.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Cookie SQL Right there with Babe Points: 715 More actions · Answer 2

Thanks, I agree with your comments.

I Still would like to understand whats going on with the cast though.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

cookie SQL (1/9/2012)
Thanks, I agree with your comments.
I Still would like to understand whats going on with the cast though.

I'd like to know as well as I cannot duplicate the problem.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184388 More actions · Answer 4

Sorry, I can duplicate it. Try this (you many want to check your language setting first):

SELECT

@@LANGUAGE AS language_name,

CONVERT(DATETIME, '2012-09-01 05:20:02.917', 121) date_using_convert,

CAST('2012-09-01 05:20:02.917' AS DATETIME) date_using_cast,

DATEPART(MONTH, CONVERT(DATETIME, '2012-09-01 05:20:02.917', 121)) AS month_using_convert,

DATEPART(mm, CAST('2012-09-01 05:20:02.917' AS DATETIME)) month_using_cast;

SET LANGUAGE british;

SELECT

@@LANGUAGE AS language_name,

CONVERT(DATETIME, '2012-09-01 05:20:02.917', 121) date_using_convert,

CAST('2012-09-01 05:20:02.917' AS DATETIME) date_using_cast,

DATEPART(MONTH, CONVERT(DATETIME, '2012-09-01 05:20:02.917', 121)) AS month_using_convert,

DATEPART(mm, CAST('2012-09-01 05:20:02.917' AS DATETIME)) month_using_cast;

SET LANGUAGE us_english;

Notice that when the language is set to british it returns the day instead of the month. I don't know why I would have thought the format of the string would have made the conversion clear. Your second set of examples work because the date is in a ISO format.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sergiy SSC Guru Points: 110192 More actions · Answer 5

cookie SQL (1/9/2012)
Thanks, I agree with your comments.
I Still would like to understand whats going on with the cast though.

CAST uses server's default DATEFORMAT settings, which are not probably the same as those you have in mind.

CONVERT allows to enforce some specific format in each particular case.

_____________
Code for TallyGenerator

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

It depends on the setting of DATEFORMAT

set dateformat mdy

select mdy =datepart(mm, cast('2012-09-01 05:20:02.917' as datetime))

set dateformat dmy

select dmy =datepart(mm, cast('2012-09-01 05:20:02.917' as datetime))

Results:

mdy

-----------

9

dmy

-----------

1

Cookie SQL Right there with Babe Points: 715 More actions · Answer 7

thanks for all the responses guys, after reading Jacks post a "lightbulb" appeared.

The culprit was :

A domain users group had been added using british english, as all users are in domain users this was causing the change.

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 8

If you use the ISO 8601 format for date/time string literals, conversions to DATETIME and DATETIME2 will not be affected by the SET LANGUAGE and SET DATEFORMAT session locale settings.

Here are the two version of the ISO 8601 format:

YYYY-MM-DDThh:mm:ss[.mmm]

YYYYMMDDThh:mm:ss[.mmm]

Jason Wolfkill

Jack Corbett SSC Guru Points: 184388 More actions · Answer 9

cookie SQL (1/10/2012)
thanks for all the responses guys, after reading Jacks post a "lightbulb" appeared.
The culprit was :
A domain users group had been added using british english, as all users are in domain users this was causing the change.

You should also be aware that other operations are affected by the Language setting. Like @@DATEFIRST and what DATEPART(weekday, date) returns. You can see this blog post for an example about how it could affect code

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question