Why Week conversion failed in SQL Server in my case?

I am trying to convert week of the date based on my criteria.

My date condition: if my @date is less than 4 AM, then @date - 1, else @date

declare @dates datetime
set @dates =  '2019-01-01 03:59:59'

select 
    case 
       when convert(varchar(26), @dates, 108) <= '04:00:00' 
          then convert(varchar, dateadd(day, -1, @dates), 103) 
          else convert(varchar, @dates, 103) 
    end BusinessDate

Output:

31/12/2018    // as expected

Now I want to find the week number of the output. So I tried

declare @dates datetime
set @dates = '2019-01-01 03:59:59'

select 
    case 
       when convert(varchar(26), @dates, 108) <= '04:00:00' 
          then convert(varchar, dateadd(day, -1, @dates), 103) 
          else convert(varchar, @dates, 103) 
    end BusinessDate,
    case 
       when convert(varchar(26), @dates, 108) <= '04:00:00' 
          then datepart(week, convert(datetime, convert(varchar, dateadd(day, -1, @dates), 103))) 
          else datepart(week, convert(datetime, convert(varchar, @dates, 103))) 
    end weeks

But I get this error:

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

Answer

Just subtract four hours:

select datepart(week,
                dateadd(hour, -4, @dates)
               )

Advertisement

Answer