Post reply

Converting DATE from ORACLE to SQL Server

  • 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.

  • 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.

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply