SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


concatenating date and time field


concatenating date and time field

Author
Message
rs80
rs80
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 407
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?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146213 Visits: 19425
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
My Blog: www.voiceofthedba.com
rs80
rs80
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 407
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
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146213 Visits: 19425
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
My Blog: www.voiceofthedba.com
rs80
rs80
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 407
That's pretty cool. I'll try that out in SSIS. Thanks
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51496 Visits: 21161
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
lshanahan
lshanahan
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1511 Visits: 438
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
rs80
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 407
I ended up writing this code:

(DT_DATE)((DT_STR,30,1252)TradeDate + " " + (DT_STR,30,1252)MessageTime)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: Administrators
Points: 146213 Visits: 19425
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search