Post reply

Data Transformation

  • July 11, 2007 at 10:59 am

    #175222

    I have an comma-separated source file that includes data going into columns defined as smalldatetime.  Some of the input dates don't exist.  I have tried scripting and derived columnsboth with the same results.  As soon as the first row with a missing date is encountered the process stops with the message, "Conversion failed because the data value overflowed the specified type.".

    The expression I am using for the derived column is:

    ISNULL(LaborRateEffectiveData) ? ActivationDate : LaborRateEffectiveDate

    The data for ActivationDate is just fine and goes into a column defined as smalldatetime as well. 

    I am replacing the column. The data type for the derived column is database timestamp.

    Can someone please tell me what I'm doing wrong?

  • July 12, 2007 at 5:57 am

    #718925

    Hi Tim,

    Instead of checking for NULL, try checking for an empty string like the following in the derived column expression:

    [LaborRateEffectiveDate] == '' ? [ActivationDate] : [LaborRateEffectiveDate]

    Let me know if it worked.

    Regards,

    Gogula

  • July 12, 2007 at 9:17 am

    #719010

    Nope, didn't work.  I still get the trancation error message.  I did come up with a work-around by comparing YEAR([LaborRateEffectiveDate]) < 1900.

  • July 12, 2007 at 2:23 pm

    #719097

    I'm stuck on the same error.

    Hopefully someone will find a better way but for now i use a staging table where i move the date into varchar(8) and then i run an insert and convert it to date. then i run an update statement which removes all the 1/1/1900. '1/1/1900 12:00:00 AM' appear when transferring an empty date field from a flatfile to a table with a datetime datatype.

    update tablename set COLUMN=null where onsaledate = '1/1/1900 12:00:00 AM'

     

     

    Alex S

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

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