Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    3

    Unanswered: Problem converting CHAR to DATETIME

    hello all,

    I am having a problem calculating the difference, in days, between two dates, STARTDATE and ENDDATE. The data is stored in the database as char(8), formatted YYYYMMDD. "Null" values are stored as '00000000'. When I try to use DATEDIFF an exception is thrown: "the conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

    How can I fix this, and a get a result even if STARTDATE or ENDDATE is '00000000'? Changing the format of the stored data is not an option.

    Thanks for any assistance.

    Mike


    Code:
     
    CREATE TABLE dbo.DATETIME1 (
    ID1 int,
    STARTDATE char(8),
    ENDDATE char(8)
    )
    INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
    VALUES (
    1, '20070105', '20070108'
    )
    INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
    VALUES (
    2, '20070105', '00000000'
    )
    Select * from DATETIME1
    Select DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) ) as Difference
     from DATETIME1
     WHERE ID1 = 1

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    How about a case statement to check for the '00000000'? Or an isdate check?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2007
    Posts
    3
    Ahhhh, good idea...can you help with the syntax of the CASE statement in SQA? I've done them in VB.NET, but not directly in SQL.

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    add this to your test code replacing your datediff select.

    Code:
     
    select STARTDATE, ENDDATE, 
    CASE
       WHEN isdate(ENDDATE) = 0 
           THEN 0
       ELSE
            DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) )
    END
     from DATETIME1 as DaysDifference

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jan 2007
    Posts
    3
    THANKS! I was doing something similar, but couldn't quite get it to work!

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    You could also try the following:
    Code:
    ISNULL(DATEDIFF(d, CONVERT(DATETIME, STARTDATE, 112), CONVERT(DATETIME, NULLIF(ENDDATE, '00000000'), 112), 0)
    Btw: explicit conversions are always better than implicit ones

Posting Permissions

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