Populating Oracle Timestamp values into Sql Server Datetime
This is not as easy as melting the ice. If we do not know how to CAST or convert, we may end up googling around for days.. My special thanks to Gidwani, Sunil (Project Lead, KPIT) and Umachandar who literally answered this issue on MSDN forums.
1) First of all we must know the valid ranges supported by Oracle timestamp and Sql Server Datetime.
2) Understand that: "Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation. - Jayashankar,Umachandar "
Now Switch to one of these Solutions to cast to Sql Server Datetime:
3) Try to work from oracle side by casting the timestamp columns to return the valid format and range required to populate Sql server Datetime Columns.
"Using Oracle Sql, Use Extract function to extract year, Validate the year, Use Decode Function to subtitute suitable values for the cases as shown - Gidwani, Sunil"
Solution:
REQUESTED_ETA_DATE_CUST (Oracle timestamp)
--------------------------------------------------------
Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mm:ss AM')) as REQUESTED_ETA_DATE_CUST
4) CAST the timestamp to string which is acceptable format for Sql Server Datetime, Do valid range filtering using SSIS transform.
Solution:
REQUESTED_ETA_DATE_CUST (Oracle timestamp)
--------------------------------------------------------
TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mm:ss AM')
Use an expression in derived Transform to filter out valid ranges for Sql Server Datetime as follows:
SUBSTRING([REQUESTED_ETA_DATE_CUST], 6,10) < 1753 ? (DT_DBTIMESTAMP)'01/01/1753 12:00 AM' : (DT_DBTIMESTAMP)[REQUESTED_ETA_DATE_CUST] Now change the type of the column now to DT_DBTIMESTAMP to accept the valid datetime value.
Hints:
1) The supported range for SMALLDATETIME is January 1, 1900, through June 6, 2079
2) Notice that the Millisecond part hasn't been solved here yet. If there is a need, Comment on this forum and I'll get back to you ASAP.
1) First of all we must know the valid ranges supported by Oracle timestamp and Sql Server Datetime.
2) Understand that: "Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation. - Jayashankar,Umachandar "
Now Switch to one of these Solutions to cast to Sql Server Datetime:
3) Try to work from oracle side by casting the timestamp columns to return the valid format and range required to populate Sql server Datetime Columns.
"Using Oracle Sql, Use Extract function to extract year, Validate the year, Use Decode Function to subtitute suitable values for the cases as shown - Gidwani, Sunil"
Solution:
REQUESTED_ETA_DATE_CUST (Oracle timestamp)
--------------------------------------------------------
Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mm:ss AM')) as REQUESTED_ETA_DATE_CUST
4) CAST the timestamp to string which is acceptable format for Sql Server Datetime, Do valid range filtering using SSIS transform.
Solution:
REQUESTED_ETA_DATE_CUST (Oracle timestamp)
--------------------------------------------------------
TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mm:ss AM')
Use an expression in derived Transform to filter out valid ranges for Sql Server Datetime as follows:
SUBSTRING([REQUESTED_ETA_DATE_CUST], 6,10) < 1753 ? (DT_DBTIMESTAMP)'01/01/1753 12:00 AM' : (DT_DBTIMESTAMP)[REQUESTED_ETA_DATE_CUST] Now change the type of the column now to DT_DBTIMESTAMP to accept the valid datetime value.
Hints:
1) The supported range for SMALLDATETIME is January 1, 1900, through June 6, 2079
2) Notice that the Millisecond part hasn't been solved here yet. If there is a need, Comment on this forum and I'll get back to you ASAP.
Comments
thanks for this article!
Let me just make one annotation:
The Date-Format should be:
MM/DD/YYYY hh:mi:ss AM
instead of
MM/DD/YYYY hh:mm:ss AM
Thanks for correcting me here.
Cheers
Subhash Subramanyam
eg value in oracle
10/21/2008 00:00:00
but it becomes
10/21/2008 12:10:00 am in sql
how to rectifiy this problem
that is a total difference of 10 min
I am losing miliseconds precision
if we lose miliseconds precion then timestamp is of no use and I could have used date of Oracle instead of timestamp.
can any body please help me
its urgently required
How are you? Hope You are doing well. I need your help in database Migration. I want to know that what are the issues/risks/ unexpected behaviour comes after migrating SQL Server 2005 database to 2008.Also please let me know that what are the things we need to check for Unit Testing after database migration.
With kind regards
Vishal Jharwade
don't give up and also keep posting simply because it just that is worth to look through it.
impatient to browse through alot more of your content articles, kind regards :)
Thanks a lot for your post. I am stuck with a similar problem where I am trying to insert data into Oracle Timestamp column from SQL server using Open query on Linked server. I will appreciate any ideas/work around to accomplish it.