Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2008
    Posts
    28

    Question Unanswered: Converting String to Date Format

    Hi Folks,

    I'm running MS SQL SERVER 2005. I have a table with a varchar(8) column containing what should be a valid date format for conversion. YYYYMMDD

    The column name is tdate...so I check ISDATE(tdate) on this column and sure enough all 1's (true) are returned.

    However I've tried just about everything I've used before and nothing works. I get the message. "Conversion failed when converting datetime from character string."

    I've tried -
    CONVERT (DATETIME, tdate)
    CAST (tdate AS DATETIME)
    CAST (CONVERT (VARCHAR, tdate, 112) AS DATETIME)
    ...and they all throw the same error.

    Any Ideas?

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    declare @it varchar(8)
    set @it='20081201'
    select cast(@it as datetime)

    worked for me

    Day may be too high for month, ie 29 for Feb, 31 for Nov.
    Last edited by PMASchmed; 01-27-09 at 12:40.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I bet you have a rogue date format mixed in.
    Code:
    select convert(datetime, '10/01/08', 112)
    returns the same error.


    EDIT: Did not read closely enough. You tried several other variants.

  4. #4
    Join Date
    Mar 2008
    Posts
    28
    Just to make sure I haven't been going crazy I have already tried the same code with a literal string value such as '20081201' and it works fine. It just fails when I attempt to convert values from that column.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How many values in that column? Any way to break it down, say by year?

    Code:
    select convert(datetime, tdate)
    from table
    where tdate like '2008%'

  6. #6
    Join Date
    Mar 2008
    Posts
    28
    FANTASTIC!

    Thanks for getting the old wheels going. The table has over 1 million records and were originally imported from an Access database. I assumed it must of have proper data integrity...stupid me.

    When I tried the conversion when checking for a 1 or 2 for the beggining of the year it worked fine. Which got me thinking there must be some dates that were out of range. I used substring to check if the any day were over 31 but that was ok...then I check if any months were above 12 and still no problem. Last I tried the year and then BAM I discovered a few hundred records did not even have numbers for the year.

    Now that I know that some of the data is invalid this will be a cinch!

    Thanks MCrowley,

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Hey ! what about me ??

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Thank you for playing, PMASchmed, we have some lovely parting gifts for you including a copy of DBForums.com the home game, and a year's supply of Turtle Wax (how many turtles do these people wax in a year, anyway???).

    Seriously, though, you were on the right track. It was more a matter of finding the bad apples.

  9. #9
    Join Date
    Mar 2008
    Posts
    28
    LOL...Sorry, I do appreciate all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •