Post reply

Validate Date format during data conversion

  • February 12, 2009 at 2:15 am

    #205333

    I am importing a excel file and tryinng to convert data types to match the database types.

    I have a date field in the source file. I used DB_TIMESTAMP during data conversion.

    But when I run the package, it throws error on the rows where the date field is empty (ie, there is 'NULL' in the source file)

    Also how do I make sure that the date is in mmddyyyy format?

    Please help!

    Thanks

  • February 12, 2009 at 10:44 pm

    #943354

    Hi dear,

    first of all, you tell us that you are using in which language, if you are passing null value in date then you use the javascript function.

    please feel free to communicate if any query.

    thanks

    sunil kumar

  • February 13, 2009 at 7:45 am

    #943607

    I'd suggest extracting the columns as a string and using a Derived Column Transformation with a suitable expression to check for a null value or otherwise validate it.

    The first example I found which I've used recently has the expression

    [Select fields from tblTimeTable on SELUSQL01].[UPDATE DATE] == "Open" ? (DT_DATE)"2078/12/31" : (DT_DATE)SUBSTRING([Select fields from tblTimeTable on SELUSQL01].[UPDATE DATE],1,11)

    This checked a field for the value "Open" and used the date '2078/12/31' and otherwise used the given date field.

    If the only invalid date value is a null, you can probably do something similar and check for "".

    The alternative is to load the field into a holding table as a varchar/char and then fix it up in SQL. This will take more space and be slower, but may give you better control.

    Derek

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

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