Post reply

Error Converting Datetime

  • December 14, 2004 at 2:11 pm

    #60274

    I am running a DTS package that, of many other things, updates a Date field.  Running the update against thousands of records causes it to error out with "error converting datetime..". 

    When running the process on only a few records, it does not error out and the update finishes successfully.

    The regional settings for short date was m/d/yyyy, but I do not believe that has anything to do with the problem.

    Any advice on where to check would be greatly appreciated!

  • December 14, 2004 at 3:22 pm

    #533860

    Off hand, it sounds like a data quality problem.  Most likely, some of your dates are not real dates, such as 2/29/2003.

  • December 14, 2004 at 11:30 pm

    #533887

    Are you pulling data from a non-sql server source? I've before found, especially on mainframes, that dates are set to 0000-00-00 to indicate no data.

    Does very much sound like you've got a data quality problem. Have a look at your source data and see if there's anything with a year before 1753 or an obviously invalid date

    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

  • December 15, 2004 at 3:32 pm

    #534053

    Thank you for the advice.  I'll look at the formats and take a look.  The odd thing is the inconsistencies when it sometimes will work and sometimes it will not.

    I appreciate it!

  • December 15, 2004 at 10:57 pm

    #534079

    Have the DTS package create a log file so you can see the offending data.

    I've had so many problems with importing illegal date values from a Teradata system that I now always script date columns. It's not Teradata's fault, just a client with crappy data.

    IF IsDate(DTSSource("DateField")) Then

    DTSDestination("DateField") = DTSSource("DateField")

    Else

    DTSDestination("DateField") = Null

    End If

  • December 16, 2004 at 9:58 am

    #534179

    Thank you!  Generally speaking, does the Regional Setting for Short Date ever matter on either the SQL server or client machines?  I have had a few instances where changing the date format to mm/dd/yyyy fixed the problem.

     

    Thanks again!

  • December 16, 2004 at 10:33 pm

    #534268

    Don't know whether it has much, if any, affect on the server. I have found cases where it matters on the client.

    That said, I personallty prefer the format yyyy/mm/dd as it's the one format that can't be misinterpreted

    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

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

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