July 11, 2012 at 10:39 am
I have a column in Oracle which is defined as:
QD_RDB_ENTRY_DATE" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
The value of this column in the first record is:
09-JUL-12 01.03.53.009438000 AM
I have the column in a SQL Server Table defined as Datetime2(6).
The precision is 6 which is the maximum.
When I load the table the SSIS Package fails
"Conversion failed because the data value overflowed the specified type.".
What is the best way to handle this?
I don't know of a SQL Server Data Type that can handle that level of precision?
Should I use the Oracle TO_CHAR FUnction?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 11, 2012 at 6:03 pm
something like
to_char(QD_RDB_ENTRY_DATE, 'yyyy-mm-dd 'HH24:MI:SS.FF7')
you might have to remove the 24 from the HH portion depending on how the instance of oracle is configured
Sql Server datetime2(7) is the largest fractional seconds precision
July 13, 2012 at 6:59 am
prvmine (7/11/2012)
something liketo_char(QD_RDB_ENTRY_DATE, 'yyyy-mm-dd 'HH24:MI:SS.FF7')
you might have to remove the 24 from the HH portion depending on how the instance of oracle is configured
Sql Server datetime2(7) is the largest fractional seconds precision
just a note for future reference, the '24' after 'HH' on the to_char mask specifies you want to convert the hour to a 24 hours clock instead of a 12 hours one which would require am/pm - this is not dependent on any instance setup.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 13, 2012 at 4:33 pm
I ended up going with the following:
TO_CHAR(RCT_RDB_ENTRY_DATE,'YY-MON-DD HH24:MI:SS') AS RCT_RDB_ENTRY_DATE
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply