Converting DATE from ORACLE to SQL Server

• Raj Malwade

  Mr or Mrs. 500

  Points: 594

  More actions

  August 13, 2004 at 9:48 am

  #59967

  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.

• William O'Connor

  SSC Journeyman

  Points: 78

  More actions

  August 13, 2004 at 11:46 am

  #518934

  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.

• Raj Malwade

  Mr or Mrs. 500

  Points: 594

  More actions

  August 13, 2004 at 5:44 pm

  #518985

  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.