August 13, 2004 at 9:48 am
Hi:
I am extracting data from OARCLE and loading into SQL Server. I have issues with DATE conversions. In ORACLE I have date of 01/01/0001. This date stores fine in ORACLE but when I try to load into SQL Server it gives me an error. I am using DTS for the load. My SQL Server date format is datetime. Is there a way to load this date or load NULL in place of 01/01/0001 using DTS or SQL?
Thanks for your help.
August 13, 2004 at 11:46 am
The SQL Server datetime value can only handle a minimum value of 1/1/1753. I can see two ways of handling the data, either use an ActiveX transformation to validate the data and send a Null to the field if the date is too early, or use a VarChar(8) field to store the data. The VarChar(8) won't support the date math functions, so it's not an especially good solution, unless you only need to display the value.
Edit: I checked a resources and the VB Datetime variable will not support a date of 01/01/0001. You can use CStr to convert it to a string and compare it, instead.
August 13, 2004 at 5:44 pm
William:
Thanks for your answer. I solved the problem by using DECODE on the ORACLE and coverted 01/01/0001 date to NULL at the source. It looks like this:
DECODE(to_number(substr(TO_CHAR( T$PRDT,
'YYYYMMDDHH24MISS'),1,4)),1,NULL,T$PRDT)
I could have also tested it for just for the char like:
DECODE(substr(TO_CHAR( T$PRDT,
'YYYYMMDDHH24MISS'),1,4)),'0001',NULL,T$PRDT)
Either way it works.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply