Post reply

Converting varchar to datetime - works in one SQL env, but not in another...

  • August 3, 2007 at 1:28 pm

    #118069

    Help, I'm trying to convert a varchar to a datetime.  I have this piece of data in a dts pkg doing multiple steps.  In one db server, this will work fine...

    Select  COMPANYNAME, POSTALCD, AccountName, convert(datetime,CONTREXPIRYDT) as CONTREXPIRYDT. 

    But in another server, using the same code, I get this error...

        The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Ideas?  I don't want to create a new variable etc, but I must be missing something.  Suggestions Please!!!

  • August 3, 2007 at 1:43 pm

    #724633

    Maybe you have some bad data.  Add WHERE IsDate(ContrExpirYDT) = 1 to remove any records with invalid date values.  Or do WHERE IsDate(ContrExpirYDT) = 0 to find records with invalid date values.

    Regards,
    Rubes

  • August 3, 2007 at 2:06 pm

    #724637

    Rubes,

    You are absolutely right.  I'd forgotten that w/ the previous db, I had to clean up some of the data prior to running all the packages.  It turns out it was having problems converting '000000' to a date.  I'd thought it would have just returned the 1900 date, but the 0's were my problem.

    Thanks so much for your QUICK response and help!!!

    Susan

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

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