Problem related to DATETIME conversion

Question

Problem related to DATETIME conversion

ND-511114

SSC Enthusiast

Points: 152
More actions
February 5, 2008 at 12:37 am

#120242

Here is the scenario where I am facing problem.
Source: SQL Server 2005 database - DB, Table: TAB1
Destination: SQL Server 2005 database - DB_NEW, Table: TAB1
In the source database table (DB.TAB1), I have 3 fields (Type CHAR(1), Dt DATE, Ti TIME).
In the destination database table (DB_NEW>TAB1), I have kept 2 fields (Type CHAR(1), DtTi DATETIME).
Now I want to transfer all the data of source to destination so my main task is to merge separate DATE and TIME fields into one DATETIME field.
I have tried "Derived Column" Data Transformation component in following manner:
Added new derived column: DtTime
Expression: (DT_DBTIMESTAMP)((DT_BYTES, 8)( [UPDATEDATE]) + (DT_BYTES, 4)( [UPDATETIME]))
It gives me error because '+' concatenate operation is not allowed for Byte Streams. However, following query runs smooth in SQL Query editor:
DECLARE @300 BINARY(8) SET @300 = 0x00000000 + CAST(300 AS BINARY(4))
declare @400 DATETIME SET @400 = cast(@300 as datetime)
SELECT @300, @400--CAST(@300 AS DATETIME)
Is there any way by which I can convert my data?
I am novice with SSIS.
Thanks!!
Nishant Desai.

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

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

Alexander G. SSCrazy Points: 2705 More actions · Answer 1

I have never heard about the binary way, but I'd rather try casting date and time to strings, concatenating them and casting it to datetime.

HTH

Niels Naglé Ten Centuries Points: 1058 More actions · Answer 2

I Agree with the post above.

Just cast them to string and concatenate these to cast to the datetime.

Or jou can join the dates within the query itself

Niels Naglé

ND-511114 SSC Enthusiast Points: 152 More actions · Answer 3

I am using "Derived column Transformation" in Data flow of my package.

I tried it out using the way u have mentioned in following manner:

(DT_DBTIMESTAMP)[ (DT_STR, 8)UPDATEDATE + (DT_STR,9) UPDATETIME]

Here UPDATEDATE is of DATE datatype and UPDATETIME is of TIME datatype.

Now this expression gives me parsing error.

Due to this error, I tried with binary way which i have mentioned previously. It works in query editor of SSMS but it is not working in expression that we write in "Derived column Transformation".

Please let me know if I am doing anything wrong in STRING way. Also if you know any other way by which this can be done then pls provide the information in detail.

Thanks.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 4

Use something similar to the expression below to build your derived column -

(DT_STR,2,1252)MONTH(GETDATE()) + "/" + (DT_STR,2,1252)DAY(GETDATE()) + "/" + (DT_STR,4,1252)YEAR(GETDATE())

Tommy

Follow @sqlscribe

ND-511114 SSC Enthusiast Points: 152 More actions · Answer 5

Thanks for putting the efforts in resolving my problem.

Actually I am storing integer values of date and time in source table in following way:

UPDATEDATE: Integer: values like 75602, 75591 etc (Elapsed number days since SQL Server's base date of 19000101.)

UPDATETIME: Integer: values like 3813661, 3451234 etc (Number of Milliseconds After Midnight)

And so first, i need to convert them into yyyymmdd and hhmmss formats and then I can write the expression which will convert them to strings like ''2007-02-07" and "03:20:34:345".

After doing it, I can wrtie the kinda similar expression mentioned by you.

Currently I am not able to convert 75602 into 20071229.

Do you have any idea by using which function I can do that in SSIS?

Alexander G. SSCrazy Points: 2705 More actions · Answer 6

DATEADD("millisecond", UPDATETIME, DATEADD("day",UPDATEDATE,DT_DBTIMESTAMP("1900-01-01")))

That should work.

Alexander G. SSCrazy Points: 2705 More actions · Answer 7

Sorry, here's the correct syntax:

Alexander G. (2/7/2008)

DATEADD("millisecond", UPDATETIME, DATEADD("day",UPDATEDATE,(DT_DBTIMESTAMP)"1900-01-01"))

ND-511114 SSC Enthusiast Points: 152 More actions · Answer 8

Thanks Alexander.

It worked...I had to amend some logic because all the records were added in my source database from CLARION which has some different base date n time.

Thank u for ur help.