Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Unanswered: SSIS Expression Date

    Good Day

    I have a csv file that i import using ssis to a table
    The datetime in the csv file is split over three columns
    Date, Time, AM eg:
    2013/04/02 07:53:17 AM or
    2013/04/04 01:56:58 PM

    Is it possible by using an expression in Derived Column task to convert to datetime and concatenate the three columns so that the end result will look something like

    2013-04-02 07:53:17 or
    2013-04-04 13:56:58 ??

    Thank You

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As a general rule of thumb I like to import all of my data in to a staging table and perform the necessary transformations in T-SQL.

    Here's something that will do the job. It is deliberately more verbose than required to show you each step
    Code:
    DECLARE @temp table (
       date_part char(10)
     , time_part char(8)
     , am_or_pm  char(2)
    )
    
    INSERT INTO @temp (date_part, time_part, am_or_pm)
      VALUES ('2013/04/02' ,'07:53:17' ,'AM')
           , ('2013/04/04' ,'01:56:58' ,'PM')
    
    SELECT DateAdd(hh, hours_adjustment, Cast(string_representation_of_date As datetime)) As final_result
    FROM   (
            SELECT the_year + '-' + the_month  + '-' + the_day + ' '
                 + the_hour + ':' + the_minute + ':' + the_second As string_representation_of_date
                 , hours_adjustment
            FROM   (
                    SELECT date_part
                         , SubString(date_part, 1, 4) As the_year
                         , SubString(date_part, 6, 2) As the_month
                         , SubString(date_part, 9, 2) As the_day
                         , time_part
                         , SubString(time_part, 1, 2) As the_hour
                         , SubString(time_part, 4, 2) As the_minute
                         , SubString(time_part, 7, 2) As the_second
                         , am_or_pm
                         , CASE WHEN am_or_pm = 'PM' THEN 12 ELSE 0 END As hours_adjustment
                    FROM   @temp
                   ) As date_components
           ) As string_dates
    WHERE  IsDate(string_representation_of_date) = 1 -- Add where clause to avoid invalid casts
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •