Post reply

How to convert varchar(20090303084115) to datetime

  • September 26, 2012 at 3:31 am

    #262202

    Hi,

    While loading the data from Excel file to staging table using SSIS 2005 contains one column "StartDate"

    Some of the values in "StartDate" exists as 20090303084115(It should be 2009/03/03 04:41:15).

    Some of the rows contains '18/11/2011'

    Need all the data in the column should be in format DD/MM/YYYY

    ie 03/03/2009

    Could you please give me the suggestion

    Regards

    SqlStud

  • September 26, 2012 at 6:05 am

    #1542334

    I would load the data into a a staging table which can take it all in as a varchar, then perform a conversion from the staging table to the actual table it needs inserting into.

    Also remember that SQL doesnt store dates in DD/MM/YYYY format its in YYYY-MM-DDTHH:MM:SS.SSSSS, so if you want it in DMY you will need to store it as a varchar column in the right format or get the front end app to change the formatting.

  • September 26, 2012 at 6:48 am

    #1542360

    Actually, SQL Server stores dates in a numeric format, so that part of the question doesn't matter.

    Here's how you can do the conversion in T-SQL, after pulling the data into a staging table:

    SET DATEFORMAT DMY;

    SELECT Col,

    CASE WHEN ISDATE(Col) = 1 THEN CAST(Col AS DATETIME)

    WHEN Col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    THEN STUFF(STUFF(STUFF(Col, 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')

    ELSE NULL

    END

    FROM ( VALUES ( '20090303084115'), ( '18/11/2011'), ( '03/03/2009') ) AS TVC (Col);

    The "values" piece is a Table-Value Constructor, which is SQL 2008, but that part won't matter since you'll have a real table there. The Case statement is what you need to customize to your needs.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 26, 2012 at 6:59 am

    #1542365

    Thanks Anthony

    Thanks GSquared

Viewing 4 posts - 1 through 3 (of 3 total)

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