Changing the use of GETDATE() in the entire database

by Lamak   Last Updated July 04, 2018 17:06 PM

I need to migrate an on-premises SQL Server 2017 database to an Azure SQL database, and I'm facing some challenges since there's quite a bit of limitations to go through.

In particular, since an Azure SQL database works only in UTC time (no time zones) and we need the local time, we have to change the use of GETDATE() everywhere in the database, which has proven to be more work than I anticipated.

I created a user defined function to get the local time that works correctly for my time zone:

CREATE FUNCTION [dbo].[getlocaldate]()
RETURNS datetime
    DECLARE @D datetimeoffset;
    SET @D = CONVERT(datetimeoffset, SYSDATETIMEOFFSET()) AT TIME ZONE 'Pacific SA Standard Time';

The issue I'm having trouble with is to actually change GETDATE() with this function in every view, stored procedure, computed columns, default values, other constraints, etc.

What would be the best way to implement this change?

Answers 1

What would be the best way to implement this change?

I would work the other way around. Convert all your timestamps in the database to UTC, and just use UTC and go with the flow. If you need a timestamp in a different tz, you can create a generated column using AT TIME ZONE (as you did above) that renders the time stamp in that specified TZ (for the app). But, I would seriously consider just having UTC returned to the app, and writing that logic -- the display logic -- in the app.

Evan Carroll
Evan Carroll
July 04, 2018 16:49 PM

Related Questions

Error 0x80131904 failed to load assembly id 65536

Updated September 07, 2018 17:06 PM

Using CROSS APPLY OPENJSON causes Azure to hang

Updated July 07, 2018 22:06 PM

BigData IoT table without foreign key

Updated July 09, 2018 01:06 AM

Import bacpac from SQL Azure into local server fails.

Updated February 20, 2018 09:06 AM