January 9, 2012 at 4:52 am
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
January 9, 2012 at 5:33 am
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));
January 9, 2012 at 5:49 am
Thanks, I agree with your comments.
I Still would like to understand whats going on with the cast though.
January 9, 2012 at 2:43 pm
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
January 9, 2012 at 2:52 pm
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
January 9, 2012 at 3:19 pm
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
January 9, 2012 at 3:51 pm
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
January 10, 2012 at 1:36 am
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.
January 10, 2012 at 10:00 am
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
January 10, 2012 at 11:00 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply