Post reply

date time format conversion

  • September 21, 2011 at 10:20 am

    #244207

    I know this is very silly thing but i am kind of lost. sometimes 2 + 2 = 5 happens

    script is like this

    create table test

    (

    col1 datetime

    )

    insert into test values (convert(datetime,'22/5/2011 10:09:40 PM',101))

    error:

    Msg 242, Level 16, State 3, Line 1

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

    The statement has been terminated.

  • September 21, 2011 at 10:26 am

    #1384649

    SET DATEFORMAT DMY

  • September 21, 2011 at 10:32 am

    #1384656

    The "Style" 101 you have used in the convert is US dateformat so your conversion is working as MM/DD/YYYY

    You could use style 103 if you want the dates input as dd/mm/yyyy or (preferably in my view) aways supply dates as an unambiguous string YYYYMMDD and remove the convert and style altogether.

    Mike John

  • September 21, 2011 at 10:33 am

    #1384657

    The key point here is you're misinterpreting the optional Style parameter of convert. It is not for converting a character value input into a datetime using a specific style, it's for outputting a specific style from a date/datetime format. It's entirely ignored when the input is not a date/datetime.

    If you're capturing a datetime from an external application, you should make sure it passes the value in a datetime format, rather than as a character value to avoid these kinds of conversion issues.

  • September 21, 2011 at 10:34 am

    #1384658

    Mike John (9/21/2011)

    The "Style" 101 you have used in the convert is US dateformat so your conversion is working as MM/DD/YYYY

    You could use style 103 if you want the dates input as dd/mm/yyyy or (preferably in my view) aways supply dates as an unambiguous string YYYYMMDD and remove the convert and style altogether.

    Mike John

    The style parameter has nothing to do with converting a varchar/char to a datetime.

  • September 21, 2011 at 2:07 pm

    #1384789

    HowardW (9/21/2011)

    The style parameter has nothing to do with converting a varchar/char to a datetime.

    You might want to recheck your facts. BOL says differently:

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

    Input when you convert to datetime; output when you convert to character data.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 21, 2011 at 2:50 pm

    #1384825

    drew.allen (9/21/2011)

    HowardW (9/21/2011)

    The style parameter has nothing to do with converting a varchar/char to a datetime.

    You might want to recheck your facts. BOL says differently:

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

    Input when you convert to datetime; output when you convert to character data.

    Drew

    Hmm, well you learn something new every day. What threw me is a quote in the same BOL article which says:

    Syntax for CONVERT:

    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

    and

    Date and Time Styles

    When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

    Am I missing something, or is expression not a varchar/char data type, therefore should be processed as zero. Maybe I'm misreading, or is this a mistake in BOL? (you'd think not for the amount this article is used)

    Edit: And apologies to Mike for saying you were incorrect

  • September 21, 2011 at 3:34 pm

    #1384864

    No problem Howard.

    "The man who makes no mistakes does not usually make anything." ~ Edward Phelps

    You had me double checking for a few minutes though. Re-reading BOL it is a bit open to interpretation and I spent a long time originally thinking like you that it was only relevant converting from datetime.

    Cheers

    Mike John

  • September 22, 2011 at 3:28 am

    #1385003

    Thanks for being so gracious!

    I think BOL's just plain wrong. The SQL Server 2005 article says:

    Is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or to convert character data of known date or time formats to datetime or smalldatetime data

    So either they just made a mistake removing this text, or they plan to deprecate it/make it undocumented and have only partially done so...

  • September 22, 2011 at 6:03 am

    #1385077

    As you say there are some interesting differences between versions of BOL. I hope it is just slightly confused wording though, rather than the removing it!

    Mike

  • September 22, 2011 at 10:46 am

    #1385318

    HowardW (9/21/2011)

    Maybe I'm misreading, or is this a mistake in BOL? (you'd think not for the amount this article is used)

    Manuals are not technical specs nor are they gospel. They should DESCRIBE the actual behavior, not PRESCRIBE the correct behavior.

    I always TEST before posting. Maybe you should consider the same.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 22, 2011 at 11:58 am

    #1385362

    drew.allen (9/22/2011)

    I always TEST before posting. Maybe you should consider the same.

    Drew

    I'm not sure how that comment's helpful.

    In this case, I thought I knew the functionality well enough, did a quick check on BOL before posting (which seemed to corroborate my understanding) and then posted. I know I was incorrect and quickly apologised for the fact.

    Also, I find these statements to be contradictory:

    Manuals are not technical specs nor are they gospel. They should DESCRIBE the actual behavior, not PRESCRIBE the correct behavior.

    and:

    drew.allen (9/21/2011)

    You might want to recheck your facts. BOL says differently:

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

    Input when you convert to datetime; output when you convert to character data.

    I also fundamentally disagree that the purpose of documentation isn't to be accurate. It doesn't need to describe everything, but it shouldn't contradict itself or make incorrect statements. Everyone uses BOL to check the usage of a function, should you feed parameters that the documentation says aren't used by trial and error in order to learn everything?

  • September 22, 2011 at 12:40 pm

    #1385375

    The documentation issues aside, I think Drew is saying it's easy to run a test and see if supplying a style as any affect on conversion from string to date:SET DATEFORMAT MDY -- US

    DECLARE @Foo VARCHAR(100) = '2/12/2010'

    SELECT CONVERT(DATETIME, @Foo, 110), CONVERT(DATETIME, @Foo, 103)

  • September 22, 2011 at 12:51 pm

    #1385387

    Absolutely. Which is exactly what I did once Drew pointed it out and apologised for my mistake.

  • September 22, 2011 at 2:34 pm

    #1385452

    HowardW (9/22/2011)I also fundamentally disagree that the purpose of documentation isn't to be accurate.

    No one said that it wasn't the goal. What I was trying to say is that it is unrealistic to expect it to be PERFECT. This is one case where what BOL says differs from actual results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 15 posts - 1 through 14 (of 14 total)

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