About this question
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 limitation 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
Use the SQL Server getdate tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on 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]()" Run the edited SQL file in Azure SQL to create your database objects... Execute the migration of data. Hope this helps you resolve SQL Server 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.
