locked
Need help with a weird error message RRS feed

  • Question

  • I'm getting this:

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

    I dug through my code and found all the places where I'm converting things to a date. They all pretty much look like this:
    REPLACE(CONVERT(DATE,BIRTH_DT,112), '-', '')
    or

    REPLACE(CONVERT(DATE,OPT_IN_DT,112), '-','')

    Both colums are datetime so I'm not converting any varchars. Another potential issue I found is:
    AND @DATE between DT_KEY_1 AND DT_KEY_2 <---I didn't write this.

    @DATE is a char. The two colums are ints but they are dates in the form of YYYYMMDD. Again no varchar hear either. All morning on this and I'm about to go crazy. Can someone provide some insight?

    Monday, October 15, 2012 6:25 PM

Answers

  • That logic makes no sense to me.  If you only need to convert from datetime to date, do it directly and skip the overhead of multiple string conversions.  Example:

    declare @x datetime
    set @x = '20121014 23:59:59.997'
    select @x, CAST(@x as DATE);

    In any event, the error message is specific about the conversion.  Either you are looking in the wrong place or there is an implicit conversion that you are missing. Note that your last example is irrelevant - comparison of char to int will not generate the error message you posted.   

    • Proposed as answer by Naomi N Tuesday, October 16, 2012 1:30 AM
    • Marked as answer by falcon00 Tuesday, October 16, 2012 2:12 PM
    Monday, October 15, 2012 6:59 PM
  • There was an implicit conversion but it took HOURS to find it:
    DATEADD(dd,-1,DT_KEY)

    DT_KEY is being stored as an int. I discovered that you can't cast an int to a datetime. I casted it to a char and everything worked right. That one silly line took up my entire day...
    • Marked as answer by falcon00 Tuesday, October 16, 2012 2:23 PM
    Tuesday, October 16, 2012 2:23 PM

All replies

  • That logic makes no sense to me.  If you only need to convert from datetime to date, do it directly and skip the overhead of multiple string conversions.  Example:

    declare @x datetime
    set @x = '20121014 23:59:59.997'
    select @x, CAST(@x as DATE);

    In any event, the error message is specific about the conversion.  Either you are looking in the wrong place or there is an implicit conversion that you are missing. Note that your last example is irrelevant - comparison of char to int will not generate the error message you posted.   

    • Proposed as answer by Naomi N Tuesday, October 16, 2012 1:30 AM
    • Marked as answer by falcon00 Tuesday, October 16, 2012 2:12 PM
    Monday, October 15, 2012 6:59 PM
  • The goal of that is to take:
    1959-07-26 00:00:00.000

    and turn it into:
    19590726

    Monday, October 15, 2012 7:00 PM
  • >AND @DATE between DT_KEY_1 AND DT_KEY_2 <---I didn't write this.

    The reason I pointed out that I didn't write this is because I would not compare a char to an int when I'm dealing with dates. I didn't know if there was some implicit conversion action going on that might be gumming up the works. I see that that's not the case. If that's not it and the datetime to date isn't it then what the heck am I missing?

    Monday, October 15, 2012 7:03 PM
  • Scott your method won't strip out the "-".
    Monday, October 15, 2012 7:06 PM
  • I really don't care about the datatype of the output as it's being thrown onto a text file. When I run that particular piece of code standalone without all the other 1000+ lines of logic I get no error.

    Monday, October 15, 2012 7:08 PM
  • Datetime to string conversion:

    DECLARE @DT datetime = '1959-07-26 00:00:00.000';
    SELECT CONVERT(varchar, @DT, 112);
    -- 19590726
    

    You can get string to string date conversion the following way:

    SELECT REPLACE(LEFT('1959-07-26 00:00:00.000',10),'-','');
    -- 19590726

    Note, however, there is no date check in the above.

    Use the ISDATE() function if check is necessary.

    DECLARE @DT varchar(32) = '1959-07-26 00:00:00.000';
    
    SELECT CASE WHEN ISDATE(@DT) = 1
                THEN CONVERT(varchar,CONVERT(DATE, @DT),112)
    			ELSE 'INVALID' END
    GO
    -- 19590726
    
    DECLARE @DT varchar(32) = '1959-07-32 00:00:00.000';
    
    SELECT CASE WHEN ISDATE(@DT) = 1
                THEN CONVERT(varchar,CONVERT(DATE, @DT),112)
    			ELSE 'INVALID' END
    GO
    -- INVALID

    Datetime conversion article:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012




    • Edited by Kalman Toth Monday, October 15, 2012 8:04 PM
    • Marked as answer by falcon00 Tuesday, October 16, 2012 2:12 PM
    • Unmarked as answer by falcon00 Tuesday, October 16, 2012 2:13 PM
    Monday, October 15, 2012 7:32 PM
  • Quote:  "Both colums are datetime ".  From your other posts, this statement is not accurate; the value is a string that represents date and time.  That is why there are some sticky posts to the top of the forum which discuss the best way to ask questions.  Otherwise, the tendency is to develop into a session of 50 questions (and the resulting confusion).  I suggest you continue looking at the other 1000s of lines of code; I'm guessing that there is an implicit conversion that simply is not obvious. 
    Monday, October 15, 2012 7:45 PM
  • REPLACE(CONVERT(DATE,BIRTH_DT,112), '-', '')
    or

    REPLACE(CONVERT(DATE,OPT_IN_DT,112), '-','')

    Both BIRTH_DT and OPT_IN_DT are table columns of type datetime HENCE there is no varchar conversion taking place.

    Monday, October 15, 2012 7:57 PM
  • When I run:

    SELECT REPLACE(CONVERT(DATE,MD.BIRTH_DT,112), '-', '') AS THEDATE
    FROM MD

    It kicks back 200K records and no errors. I've removed all the instances where I do extra work to strip out the "-" so there is no char to date conversion there. That leaves several joins where I am comparing varchars to dates. For instance:

    AND MSP.OPT_IN_DT = HLI.EffDate

    OPT_IN_DT is of type datetime. EffDate is of type varchar(10). When I check using ISDATE everything in EffDate comes back as a date. What is the best way to do this? Convert the date to a varchar or the other way around. Even if I do that I'm just explictly doing what is impliciting going on already anyway no?

    Monday, October 15, 2012 8:04 PM
  • CLOSING IN! When I run:
    SELECT EffDate, ISDATE(EffDate)
    FROM Table
    WHERE ISDATE(EffDate) = 0

    I discovered crap data:

    1003-07-16
    1003-07-16
    0210-04-03
    0212-01-01
    0212-01-01

    And a bunch of null values. So if I need:

    AND MSP.OPT_IN_DT = HLI.EffDate

    and EffDate is sometimes junk..hmmm.... 

    Monday, October 15, 2012 8:28 PM
  • If you want to return your datetime values as strings on the form YYYYMMDD,
    just to:

       SELECT convert(char(8), BIRTH_DT, 112)

    This conversion:

       REPLACE(CONVERT(DATE,BIRTH_DT,112), '-', '')

    First converts BIRTH_DT to the date data type. Then it is implicitly
    converted to a varchar on the form YYYY-MM-DD and then you strip the
    hyphens. That's three steps when only one is needed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 15, 2012 10:17 PM
  • There was an implicit conversion but it took HOURS to find it:
    DATEADD(dd,-1,DT_KEY)

    DT_KEY is being stored as an int. I discovered that you can't cast an int to a datetime. I casted it to a char and everything worked right. That one silly line took up my entire day...
    • Marked as answer by falcon00 Tuesday, October 16, 2012 2:23 PM
    Tuesday, October 16, 2012 2:23 PM
  • You can cast an int to datetime - unfortunately. But the cast is the equivalent to

      dateadd(dd, intval, '19000101')

    So if your integer value is 20121010, things go bad.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Tuesday, October 16, 2012 10:11 PM
    Tuesday, October 16, 2012 9:58 PM