SQL Job on server set to UTC. Are jobs that perform GETDATE using UTC?

Question

SQL Job on server set to UTC. Are jobs that perform GETDATE using UTC?

andyewx

Old Hand

Points: 347
More actions
September 12, 2023 at 12:46 pm

#4284962
This is a scenario I haven't had to consider.
We have a UTC time set SQL Server running jobs. We have set the jobs to run in based on UTC time converted from the timezone we're in (+10 hours) eg 6:45pm UTC (4:45am AEST)
However one of the jobs calls a view that uses a GETDATE calculation in a case statement to determine PAST and FUTURE values.
```
 CASE WHEN CONVERT(DATE, ServiceDate, 103) >= CONVERT(DATE,GETDATE(),103) THEN 'Future'
 ELSE 'Past'
```
My question is, is the GETDATE the UTC date?
The reason I ask is when I look at the view in SSMS the date calculation is correct but the job seems to be a day behind.
I think I may have answered my own question having written it out but I am interested in confirmation of the context of GETDATE when perfroming SSMS quesries versus automations like job executions.
regards
Andrew

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

getdate will get the current date from the server it is executed on - if you need UTC datetime as part of your processing you need to use one of the functions that returns UTC (SYSUTCDATETIME or GETUTCDATE)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

If GETDATE() in SSMS is returning UTC - then using GETDATE() in an agent job will also return UTC. If ServiceDate is stored in UTC then your calculation would be correct - but if ServiceDate is localized then you are not going to get correct results.

Most likely, the problem is the time you are running this job - which happens to cross a date boundary compared to your local time and therefore it 'appears' you are a day behind.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

andyewx Old Hand Points: 347 More actions · Answer 3

Thanks forconfirming my thoughts. I didn't mention the reference date in the table being used is in local time(+10 hours).

Therefore the UTC, UTC+10 hours comparisons were always going to be wrong.

Thanks again.

Andrew

This reply was modified 1 year, 2 months ago by andyewx.