DATETIME Query problem

  • 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

  • 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

  • Thanks, I agree with your comments.

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

  • 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.

  • 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.

  • 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

  • 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

  • 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.

  • 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

  • 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

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

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