Post reply

Date format error

  • July 4, 2008 at 1:43 am

    #193422

    Hi Guys,

    I am given a data file for data uploading process and one of the fields is a date. Occasionally, the date given to us is not in the correct format - ex 16-Jul-0121. This of course produces an error when inserting it in a datetime field in table.

    Anyone has a suggestion to trap such errors and converting them to say 01-01-1900, because such errors are failing the data upload process?

    Thanks!

  • July 4, 2008 at 2:50 am

    #838258

    Then change the datatype from datetime to nvarchar for example ..

  • July 4, 2008 at 2:51 am

    #838259

    I would sugget that you import into a staging table where the column is of type varchar, do any validation and correction that you need thre, then import into the real table where the data types are correct.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 4, 2008 at 2:51 am

    #838260

    I cannot do this as i must use the date in question for querying in reports etc

  • July 4, 2008 at 3:15 am

    #838268

    Do you have any suggestions how to achieve the result? how to reformat the year 0121 ?

  • July 4, 2008 at 3:34 am

    #838275

    Jonathan Mallia (7/4/2008)

    I cannot do this as i must use the date in question for querying in reports etc

    You said you wanted to change the dates to something like '1900/01/01'?

    If so, import into a staging table, run a query that updates all rows where ISDATE(SomeColumn)=0 to your default date, then load the data into the real table. It can all be done in the import process.

    If you need to keep the out-of-range values, make the column of type varchar, but then you need to decide what those incorrect values mean.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 4, 2008 at 4:50 am

    #838307

    Thanks a lot Gila,,,Sounds like a pretty good strategy to me...

Viewing 7 posts - 1 through 6 (of 6 total)

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