C#•5mo ago

SQL Sever: Changing getdate() to getutcdate()

This question is more related to SQL, but I wasn't sure where else to ask. Hope that's okay. I have a SQL Server in AEST/AEDT that has a table which has a default value of getdate() in a CreatedDate column of type DateTime. I want to change this to getutcdate(), and I want to change all the current date values for that column from their AEST/AEDT value to UTC. Of course, because they have Daylight Savings in Australia, this won't be easy. In fact, it may not be possible to be 100% accurate, but I think I can get close. Theoretically, I know that any date between 2am first Sunday in October and 3am first Sunday in April will be ADST, and anything else is AEST. Which means I can just run a dateadd for each year, like so:

--2022/2023 AEDT
update TableA
set CreatedDate = DATEADD(HOUR, -11, CreatedDate)
where CreatedDate between '2022-10-02 02:00:00.000' and '2023-04-02 02:59:59.999'

--2023 AEST
update TableA
set CreatedDate = DATEADD(HOUR, -10, CreatedDate)
where CreatedDate between '2023-04-02 03:00:00.000' and '2023-10-01 01:59:59.999'

--2022/2023 AEDT
update TableA
set CreatedDate = DATEADD(HOUR, -11, CreatedDate)
where CreatedDate between '2022-10-02 02:00:00.000' and '2023-04-02 02:59:59.999'

--2023 AEST
update TableA
set CreatedDate = DATEADD(HOUR, -10, CreatedDate)
where CreatedDate between '2023-04-02 03:00:00.000' and '2023-10-01 01:59:59.999'

The only problem is that for any dates between 2am and 3am on 2023-04-02, it's impossible to know if they occurred in AEST or AEDT. But I'm honestly not worried about it; I don't need to be 100% accurate. I just wanted to make sure there isn't something I'm not thinking of. Maybe someone else has had to do this before? I couldn't find anything in google. Any help would be appreciated, thank you!

4 Replies

Mayor McCheese•5mo ago

Seems reasonable, is there a significant cost to being wrong?

Camster•5mo ago

Not really, it's a small app with minimal impact

Mayor McCheese•5mo ago

go for it ¯\_(ツ)_/¯

Camster•5mo ago

haha, thanks 😄

Gaming

Programming

SQL Sever: Changing getdate() to getutcdate()