September 15, 2009 at 11:47 am
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!
September 15, 2009 at 11:55 am
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
September 15, 2009 at 11:58 am
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
September 15, 2009 at 12:04 pm
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
September 15, 2009 at 12:51 pm
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
September 15, 2009 at 1:35 pm
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
September 15, 2009 at 1:56 pm
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) )
September 16, 2009 at 7:50 am
r_prasanna82 (9/15/2009)
HiI 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
September 16, 2009 at 9:22 am
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy