concatenating date and time field

  • The OledbSource has a date field and time field. The OleDbDestination has the field as a datetime. Do I have to do something similar to this:

    (DT_DBTIMESTAMP)(SUBSTRING( [TradeDate] ,1,4) + "-" + SUBSTRING([TradeDate],5,2) + "-" + SUBSTRING([TradeDate],7,2) + " " + SUBSTRING( [MessageTime] ,1,2) + ":" + SUBSTRING( [MessageTime] ,3,2) + ":" + SUBSTRING( [MessageTime] ,5,2) + ":" + SUBSTRING( [MessageTime] ,7,3) +"." + SUBSTRING( [MessageTime] ,10,5))

    Or is there an easier way?

  • You have to provide more information. What are the data types of the source fields and DDL?

    It might be as simple as concatenating the fields and converting to date time.

  • Sorry about that.

    For the date field the data type is date and the time field the data type is time. The destination has the field as datetime. So MessageDate is date and TradeDate is time(7)

    I'm not sure how to provide the DDL. If you give me an example of a DDL I can provide it.

  • DDL would be the table create. Here's a working example in T-SQL

    use sandbox;

    go

    create table Test

    ( id int

    , shipdate date

    , shiptime time

    )

    ;

    go

    insert Test select 1, '1/1/2013', '4:45pm';

    go

    select * from test

    select

    shipdate

    , shiptime

    , cast( shipdate as datetime)

    , cast( shiptime as datetime)

    , DateAdd(d, DateDiff(d, 0, Cast(ShipDate As datetime)), Cast(ShipTime as datetime))

    from test

    drop table test;

    In SSIS, I assume you have some DATEADD, DATEDIFF, CAST functions you can use. Or put this query in your task.

  • That's pretty cool. I'll try that out in SSIS. Thanks

  • Or put this query in your task.

    If your source is SQL Server, this is what I would do - use a query for your data source and include this expression in the query.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • In SSIS, I assume you have some DATEADD, DATEDIFF, CAST functions you can use. Or put this query in your task.

    SSIS does have DATEADD(), DATEDIFF(), and DATEPART() as well as MONTH(), DAY() and YEAR() functions available via the Derived Column Transformation. DATEPART will return hours, minutes, seconds and milliseconds and is the best bet to avoid all the SUBSTRING()s, which can get problematic if a date/time gets out of the expected format. Syntax and usage details are in the Integration Services section of BOL.

    ____________
    Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

  • I ended up writing this code:

    (DT_DATE)((DT_STR,30,1252)TradeDate + " " + (DT_STR,30,1252)MessageTime)

  • rs80 (8/2/2013)


    I ended up writing this code:

    (DT_DATE)((DT_STR,30,1252)TradeDate + " " + (DT_STR,30,1252)MessageTime)

    Nice, and thanks for the update.

Viewing 9 posts - 1 through 8 (of 8 total)

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