Need to Add year using Dateadd function

Question

Post reply

Need to Add year using Dateadd function

r_prasanna82

SSC-Addicted

Points: 456
More actions
September 15, 2009 at 11:47 am

#135023

Hi All,
I am transferring data from Oracle to sybase using ssis. For one specific column i am facing issue. i.e. In front end users entered the date as 22/10/05 (mm/dd/yyyy - instead of typing 2005 they have typed 05) in oracle the data has been entered as 22/10/0005. So when i transfer data from oracle to sybe its not accepting the year.
So wat i am planning is to add 2000 with the year alone. I am not sure whether it works. But i need your suggestions.
I tried with teh derived column in ssis between the source (oracle) and destination (Sybase) and tried teh dateadd function for that specific column. But somehow i ma missing something in the expression so i am not able to get it correct.
The format from the source is mm/dd/yyyy time.
Please find the error out i am facing.
"Error occurred column_date =10/26/0005 12:00:00.000 AM"
Please do the needful.
Kindly let me know how to go about the expression to add 2000 with this. So that i can proceed further.
Thanks in advance
Thanks!

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

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

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

What I would do is add a derived column using only the 2 digits of the year.

In example the dates 22/10/2005 or 22/10/0005 would be trimmed as 22/10/05.

Then would be able to cast the output string as datetime, and handle both scenarios. SQL Server will handle the 2 digits date, and interpret it as 2005.

Because, if you do add 2000 to the year everytime, it might result in having a date as of 22/10/4005...

This is what I would do. There might be better options.

Cheers,

J-F

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 2

Thanks for your reply.

This one sounds better than mine. Is there any specific function in ssis where i can change this yyyy to yy. Sorry for being so dump. I am new to this area. Could you please help me in this regard.

Thanks

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

You can use the Derived column transformation, and use a substring function with your date field.

Something like this

substring([DateString],1,6) + substring([DateString],9,2)

Add it as a new column to check the output, and do the data validation, then you can cast it as a Datetime value [DT_Date], or something like that.

When you're sure your data is correct, you can replace the Date Column.

Hope that helps,

Edit: Removed the select because that is not TSQL :hehe:

Cheers,

J-F

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 4

Thank you so much for the reply. I will work on it and will let you know the output.

If you have some other option also please let me know.

Thanks

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 5

Hi

I tried the same but my bad its not working. It says substring does not support the data type DT_TIMESTAMP.

I tried like

(DB_DATE)(SUBSTRING(columnname, 6,4) + '-' + SUBSTRING(columnname, 4,2) + '-' + SUBSTRING(columnname, 1,2) )

Please correct me if i ma wrong.

Thanks

mcha7628 SSC Veteran Points: 265 More actions · Answer 6

r_prasanna82 (9/15/2009)
The format from the source is mm/dd/yyyy time.

YYYY-MM-DD

(DB_DATE)(SUBSTRING(columnname, 7,4) + '-' + SUBSTRING(columnname, 1,2) + '-' + SUBSTRING(columnname, 4,2) )

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

r_prasanna82 (9/15/2009)
Hi
I tried the same but my bad its not working. It says substring does not support the data type DT_TIMESTAMP.
I tried like
(DB_DATE)(SUBSTRING(columnname, 6,4) + '-' + SUBSTRING(columnname, 4,2) + '-' + SUBSTRING(columnname, 1,2) )
Please correct me if i ma wrong.
Thanks

Is it possible your columnName is already in a DT_TimeStamp format? Substring is used on a string (varchar). That might explain why you are getting this error.

Cheers,

J-F

r_prasanna82 SSC-Addicted Points: 456 More actions · Answer 8

Hi All,

First of all for the question yes already its in DT_TimeStamp format

Thanks for the info. Actually i tried with a CAST query and it worked for me.

The query i used was TO_DATE REPLACE(CASTcolname as char, '000', '200'))

This one changed the year 000 to 200 and as of now the issue got resolved. Now i will try your solution since that is the best one for long term go. I spent too much time writing the query using the DATEADD hence i tried the other way around.

Thanks anyway will try ur idea as well and will let u know for any help.

Thanks so much for all ur help.

Thanks