1

I am trying to convert a temporary variable into UTC time and print it using the following,

declare @dt datetime, @dtEnd datetime
set @dt = '2017-04-25' AT TIME ZONE 'UTC' 
set @dtEnd = '2017-04-30' AT TIME ZONE 'UTC' 

select dateadd(day, number, @dt)
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd

I got this code from the accepted answer here. I am getting an error,

Argument data type varchar is invalid for argument 1 of AT TIME ZONE function.

I am using SQL Server docs to convert from CDT to UTC. Linked answer works great to just print without AT TIME ZONE 'UTC'.

How can I convert a temporary variable to print in UTC time?

i.n.n.m
  • 2,936
  • 7
  • 27
  • 51

1 Answers1

2

You need to use cast as datetime

declare @dt datetime, @dtEnd datetime
set @dt = CAST('2017-04-25' AS DATETIME) AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC' 
set @dtEnd = CAST('2017-04-30' AS DATETIME) AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC' 

select dateadd(day, number, @dt)
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd
i.n.n.m
  • 2,936
  • 7
  • 27
  • 51
Fatemeh Sheikhha
  • 66
  • 5
  • You're right casting works, but for my scenario, I edited it to use `at time zone 'Central Standard Time' AT TIME ZONE 'UTC'`. – i.n.n.m Dec 14 '17 at 00:25