Post reply

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

  • March 10, 2016 at 8:45 am

    #325617

    DECLARE @Date datetime

    SET @Date = '2016-03-10 00:00:00.000'

    The code below giving out of range error

    DECLARE @Start datetime,

    @End datetime

    set @Start = CASE datepart(mi, @Date) when 0 then cast(convert(varchar(10), @date, 1) + ' ' + cast(datepart(hh, @date) - 1 as varchar(2)) + ':45:00.000' as datetime)

    when 15 then cast(convert(varchar(10), @date, 1) + ' ' + cast(datepart(hh, @date) as varchar(2)) + ':00:00.000' as datetime)

    when 30 then cast(convert(varchar(10), @date, 1) + ' ' + cast(datepart(hh, @date) as varchar(2)) + ':15:00.000' as datetime)

    when 45 then cast(convert(varchar(10), @date, 1) + ' ' + cast(datepart(hh, @date) as varchar(2)) + ':30:00.000' as datetime) end

    select @Start

  • March 10, 2016 at 9:01 am

    #1864270

    If the hour is 00 the datepart returns 0, 0 -1 = -1.

    cast(datepart(hh, @date) - 1 as varchar(2))

  • March 10, 2016 at 9:04 am

    #1864273

    Tell us what you're trying to do and I but someone can come up with a solution which that requires less code and performs much better. All that CASTing and CONVERTing is not necessary, makes for tougher code to debug and hurts performance.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • March 10, 2016 at 9:13 am

    #1864279

    Use proper date arithmetic instead of messing around with character conversions:

    SET @Start =CASE WHEN DATEPART(mi,@Date) IN (0,15,30,45)

    THEN DATEADD(mi,DATEDIFF(mi,0,DATEADD(mi,-15,@Date)),0) -- subtracts 15 minutes and strips off s and ms

    END

    SELECT @Start

    John

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

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