Azure SQL - Convert time zone to 'Central Standard Time'?

Question

Server time is 'UTC' and cannot be changed due to my database being Azure SQL. I know you can convert to other time zones. However, I'm not sure how to apply this correctly.

In short. I need to be able to pull and job based on 'start date' but by Central Standard Time.

I did comment out where I tried some things. My issue is, I'm not sure how to apply the offset to the 'Start Date' for what is returned in the front end.

The 'WHERE' statement does technically work to only return 'Today's Jobs', however returns based on UTC time which won't work for people using this in Central Standard Time Zone. How do I offset this properly?

Much appreciation for the help.

begin
--declare @dto datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-6:00');
--DECLARE @dto datetimeoffset
--SET @dto = (SELECT GETUTCDATE() AT TIME ZONE 'Central Standard Time')
--SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00')

declare @today datetimeoffset;
--set @today = switchoffset (convert(time, getdate()), '-05:00');
set @today = getdate();

SELECT dbo.Projects.ProjectID, dbo.Projects.ProjectName, dbo.Jobs.JobID, dbo.Jobs.Status, dbo.Jobs.StartDate, dbo.Jobs.EndDate, dbo.Jobs.CompletedDate, dbo.JobType.JobTypeID, dbo.JobType.JobTypeDescription
FROM dbo.Jobs INNER JOIN dbo.Projects ON dbo.Jobs.ProjectID = dbo.Projects.ProjectID
INNER JOIN dbo.JobType ON dbo.Jobs.JobTypeID = dbo.JobType.JobTypeID

WHERE convert(varchar(10), StartDate, 102) 
    = convert(varchar(10), @today, 102)

ORDER BY JobID DESC

End

Answer 1

Azure SQL database always follows UTC. Use AT TIME ZONE in Azure SQL Database if you need to convert date and time information in a non-UTC time zone.

AT TIME ZONE converts input date to target time zone. It returns datetimeoffset value in the target time zone.

Query:

declare @current_cst datetimeoffset;
set @current_cst = (SELECT getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time')

declare @current_utc datetimeoffset;
set @current_utc = getutcdate();

--retunrs datetimeoffset format
select @current_utc current_utc, @current_cst current_cst 

--retunrs 102 format(yyyy.mm.dd)
select convert(varchar(10), @current_utc, 102) as current_utc, convert(varchar(10), @current_cst, 102) as current_cst 

Note: A list of installed time zones are available in sys.time_zone_info view.