Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

concatenating date and time field Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 10:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:57 PM
Points: 99, Visits: 308
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?
Post #1480016
Posted Thursday, August 1, 2013 11:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:52 PM
Points: 33,268, Visits: 15,440
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480039
Posted Thursday, August 1, 2013 12:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:57 PM
Points: 99, Visits: 308
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.
Post #1480077
Posted Thursday, August 1, 2013 12:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:52 PM
Points: 33,268, Visits: 15,440
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480091
Posted Thursday, August 1, 2013 1:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:57 PM
Points: 99, Visits: 308
That's pretty cool. I'll try that out in SSIS. Thanks
Post #1480114
Posted Friday, August 2, 2013 12:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1480253
Posted Friday, August 2, 2013 6:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 135, Visits: 250
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.
Post #1480355
Posted Friday, August 2, 2013 8:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:57 PM
Points: 99, Visits: 308
I ended up writing this code:

(DT_DATE)((DT_STR,30,1252)TradeDate + " " + (DT_STR,30,1252)MessageTime)
Post #1480443
Posted Friday, August 2, 2013 10:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:52 PM
Points: 33,268, Visits: 15,440
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse