August 1, 2013 at 10:05 am
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?
August 1, 2013 at 11:00 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 1, 2013 at 12:24 pm
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.
August 1, 2013 at 12:42 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 1, 2013 at 1:25 pm
That's pretty cool. I'll try that out in SSIS. Thanks
August 2, 2013 at 12:34 am
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.
August 2, 2013 at 6:29 am
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.
August 2, 2013 at 8:46 am
I ended up writing this code:
(DT_DATE)((DT_STR,30,1252)TradeDate + " " + (DT_STR,30,1252)MessageTime)
August 2, 2013 at 10:57 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply