SQL Server – Changing the Use of GETDATE() in the Entire Database

azure-sql-databasesql serversql-server-2017

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
AS
BEGIN
    DECLARE @D datetimeoffset;
    SET @D = CONVERT(datetimeoffset, SYSDATETIMEOFFSET()) AT TIME ZONE 'Pacific SA Standard Time';
    RETURN(CONVERT(datetime,@D));
END

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?

We are in the public preview of Managed Instances. It still has the same issue with GETDATE(), so it doesn't help with this problem. Moving to Azure is a requirement. This database is used (and will be used) always in this time zone.

Best Answer

  1. Use the SQL Server tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on

  2. Edit the SQL file (make a backup first) using any text editor that allows you to find the text "GETDATE()" and replace it with "[dbo].[getlocaldate]()"

  3. Run the edited SQL file in Azure SQL to create your database objects...

  4. Execute the migration of data.

Here you have a reference from azure documentation: Generating Scripts for SQL Azure

Related Question