Post reply

SSIS 2008 "Split Datetime column into year, month, day, hr, sec etc and store each of it inoto seprate columns"

  • August 23, 2011 at 3:54 am

    #256054

    I have a project on Data warehouse and On ETL phase I also want to get datetime from a data source table column and split that datetime column into year, month, day, hr, sec, etc

    Example

    i have a datime column and in it suppose date and time is "2011-05-01 12:55:45.000"

    on ETL I want to split this datetime record and want to put them in multiple columns

    like

    2011 goes to year column

    05 goes to month column

    01 goes to date column

    12 goes to hr column

    55 goes to min column

    45 goes to sec column etc

    How can I do it please tell me As soon as Possible

    regards

    Tahir.

  • August 23, 2011 at 4:04 am

    #1371851

    Create a new data flow and use a derived column transformation to split the date into its constituent parts.

    John

  • August 23, 2011 at 5:05 am

    #1371876

    John

    according to u I am using derived column transformation now

    from columns i have derived datetime column but what to write in expression can u tell be that

  • August 23, 2011 at 5:09 am

    #1371882

    The name of the date time column from source file is "dates"

    in expression it is [dates] but how to splitt it which function of date/time to use and how to use etc

    plz I need to solve this problem ASAP

  • August 23, 2011 at 6:19 am

    #1371924

    In the Derived Column Transformation Editor, there's a list of date/time functions. Pick one of those. If you're unsure of the syntax, use a search engine - there's plenty of material out there on the web.

    John

  • August 23, 2011 at 6:36 am

    #1371937

    tahirayoub (8/23/2011)

    The name of the date time column from source file is "dates"

    in expression it is [dates] but how to splitt it which function of date/time to use and how to use etc

    plz I need to solve this problem ASAP

    As John says, SSIS is reasonably helpful in that it lists functions for you.

    Which you can use to build up the derived data that you require.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 6 (of 6 total)

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