Home Forums SQL Server 2008 SQL Server Newbies how to insert datetime column into table RE: how to insert datetime column into table

  • March 27, 2013 at 5:06 am

    #1601078

    Steve JP (3/27/2013)

    SELECT DateAsCharacter = CONVERT(VARCHAR(25),GETDATE(),100)

    -- 'Mar 26 2013 12:49PM'

    SELECT DateAsDate = CONVERT(DATETIME,'Mar 26 2013 12:49PM',100)

    -- 2013-03-26 12:49:00.000

    Dont totally agree with this as the following will error which is as I expect because the style is not vailid

    -- Just to 100% prove the style is invalid

    SELECT DateAsCharacter = CONVERT(VARCHAR(25),GETDATE(),25510)

    Msg 281, Level 16, State 1, Line 1

    25510 is not a valid style number when converting from datetime to a character string.

    - and your point is?

    But the following seems to want to ignore the style arguement.

    SELECT[DateAsDate AM] = CONVERT(DATETIME,'Mar 26 2013 02:49AM' )

    SELECT[DateAsDate PM] = CONVERT(DATETIME,'Mar 26 2013 02:49PM' )

    SELECT[DateAsDate mm-dd-yy] = CONVERT(DATETIME,'Mar 26 2013 02:49PM' , 110 )

    SELECT[DateAsDate yyyymmdd] = CONVERT(DATETIME,'Mar 26 2013 02:49PM' , 112 )

    SELECT[DateAsDate No Such format] = CONVERT(DATETIME,'Mar 26 2013 02:49PM' ,25510) -- invalid style so Error? or Ignore?

    DateAsDate AM = 2013-03-26 02:49:00.000

    DateAsDate PM = 2013-03-26 14:49:00.000

    DateAsDate mm-dd-yy = 2013-03-26 14:49:00.000

    DateAsDate yyyymmdd = 2013-03-26 14:49:00.000

    DateAsDate No Such format = 2013-03-26 14:49:00.000

    The style argument for converting a date as string in the format 'Mar 26 2013 12:49PM' to a datetime data type using CONVERT() is 100. Other style arguments are likely to fail. I'm sorry but I still don't see your point. None of this appears to prove anything either way. Have a quick read of CAST and CONVERT in BOL, specifically CONVERT relating to dates. The explanation is quite good. If you're still unsure, post back.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden