concatenating date and time field

Question

Post reply

concatenating date and time field

rs80

SSCrazy

Points: 2312
More actions
August 1, 2013 at 10:05 am

#275840

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?

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

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

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.

rs80 SSCrazy Points: 2312 More actions · Answer 2

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.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

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.

rs80 SSCrazy Points: 2312 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

shoestringdba SSCertifiable Points: 6226 More actions · Answer 6

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.

rs80 SSCrazy Points: 2312 More actions · Answer 7

I ended up writing this code:

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

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.