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!

C#•3y ago•

4 replies

Camster

SQL Sever: Changing getdate() to getutcdate()

--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'

SQL Sever: Changing getdate() to getutcdate()

Similar Threads

SQL Sever: Changing getdate() to getutcdate()

Similar Threads

Similar Threads

Similar Threads