Changing the use of GETDATE() in the entire database

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

Erik Darling · Accepted Answer · 2018-07-05 13:38:59Z

17

+200

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

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

edited Jul 5 '18 at 13:38

Erik Darling

29.6k13 gold badges89 silver badges146 bronze badges

answered Jul 4 '18 at 19:17

AMG

1,2021 silver badge10 bronze badges

Though in practice this approach is more complicated than it sounds, it is probably the correct and best answer. I've had to do tasks similar to this many scores of times and I've tried every approach available and I haven't found anything better (or even close, really). the other approachs seem great at first, but they quickly become a nightmarish quagmire of oversights and gotchas. – RBarryYoung Jul 11 '18 at 18:14

add a comment |

Evan Carroll · Accepted Answer · 2018-07-04 16:51:36Z

15

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.

edited Jul 4 '18 at 16:51

answered Jul 4 '18 at 16:49

Evan Carroll

41.4k15 gold badges104 silver badges286 bronze badges

if it was just a database thing, I may consider this, but that change affects a lot of other apps and software that would need serious refactoring. So, sadly, it isn't a choice for me – Lamak Jul 4 '18 at 16:51
5

What guarantee will you have that none of the "apps and software" use getdate() ? i.e. sql code embedded in the apps. If you can't guarantee that, refactoring the database to use a different function will just lead to inconsistency. – Mister Magoo Jul 5 '18 at 0:43
@MisterMagoo It depends on the practices at the shop, quite frankly I think this is a very minor concern, and I can't see it taking as much time to ask the question to work around the problem then to actually fix i. It would be interesting if this question wasn't Azure, because I could hack it and give you more feedback. Cloud stuff sucks though: they don't support it, so you have to change something on your side. I would prefer to go the route provided in my answer and spend the time on doing it right. Also, you have no guarantees anything will work when you move to Azure, as always tias. – Evan Carroll Jul 5 '18 at 0:58
@EvanCarroll, sorry I just re-read my comment and I did not express my intent well! I meant to be supportive of your answer (upvoted) and raise the point that suggestions of just changing the use of getdate() to getlocaldate() in the database would be leaving them open to inconsistencies from the app side, and moreover is just a sticking plaster on a bigger problem. 100% agree with your answer, fixing the core problem is the right approach. – Mister Magoo Jul 5 '18 at 6:41
@MisterMagoo I understand your concern, but in this case, I can guarantee that apps and software interact with the database only through stored procedures – Lamak Jul 5 '18 at 15:07

| show 3 more comments

David Spillett · Accepted Answer · 2018-07-10 11:26:45Z

Rather than export, manually edit, and rerun, you could try do the job directly in the database with something like:

DECLARE C CURSOR FOR
        SELECT sm.definition, so.type
        FROM   sys.objects so
        JOIN   sys.all_sql_modules sm ON sm.object_id = so.object_id
        WHERE  so.type IN ('P', 'V')
        ORDER BY so.name
DECLARE @SQL NVARCHAR(MAX), @ojtype NVARCHAR(MAX)
OPEN C
FETCH NEXT FROM C INTO @SQL, @ojtype
WHILE @@FETCH_STATUS = 0 BEGIN
    IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE') 
    IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW'     , 'ALTER VIEW'     ) 
    SET @SQL = REPLACE(@SQL, 'GETDATE()', '[dbo].[getlocaldate]()') 
    --PRINT @SQL
    EXEC (@SQL)
    FETCH NEXT FROM C INTO @SQL, @ojtype
END
CLOSE C
DEALLOCATE C

of course extending it to deal with functions, triggers, and so forth too.

There are a few caveats:

You may need to be a bit brighter and deal with different/extra white-space between CREATE and PROCEDURE/VIEW/<other>. Rather than the REPLACE for that you might prefer to instead leave the CREATE in place and execute a DROP first, but this risks leaving sys.depends and friends out of kilter where ALTER may not, also if ALTER fails you at least have the existing object still in place where with DROP+CREATE you may not.
If you code has any "clever" smells like modifying its own schema with ad-hoc TSQL then you'll need to make sure the search and replace for CREATE->ALTER doesn't interfere with that.
You will be wanting to regression test the entire application(s) after the operation, whether you use the cursor or export+edit+run methods.

I've used this method to make similar schema-wide updates in the past. It is a bit of a hack, and feels quite ugly, but sometimes it is the easiest/quickest way.

Defaults and other constraints can be modified similarly too, though those can only be dropped and recreated rather than altered. Something like:

DECLARE C CURSOR FOR
        SELECT AlterDefaultSQL = 'ALTER TABLE [' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'
                               + CHAR(10)
                               + 'ALTER TABLE [' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', '[dbo].[getlocaldate]()')+' FOR '+sc.name+';'
        FROM   sys.tables st
        JOIN   sys.default_constraints si ON si.parent_object_id = st.object_id
        JOIN   sys.columns sc ON sc.default_object_id = si.object_id
DECLARE @SQL NVARCHAR(MAX)
OPEN C
FETCH NEXT FROM C INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
    --PRINT @SQL
    EXEC (@SQL)
    FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C

Some more fun that you may need to contend with: if you are partitioning by time then those parts may need altering too. While partitioning on time more granularly then by day is rare you could have issues where DATETIMEs are interpreted by the partitioning function as being the previous or next day depending on timezine, leaving your partitions unaligned with your usual queries.

yeah, the caveats are what makes this hard. Also, this doesn't take into account column default values. Thanks anyway — Lamak, Jul 5 '18 at 13:40
Column default values and other constraints can also be scanned for in the sys schema and programmatically modified. — David Spillett, Jul 5 '18 at 14:21
Maybe replacing with e.g. CREATE OR ALTER PROCEDURE helps around some code-generation issues; still there may be issues as the stored definition will read CREATE PROCEDURE (three! spaces) and this is neither matched by CREATE PROCEDURE nor CREATE OR ALTER PROCEDURE… ._. — TheConstructor, Jul 10 '18 at 18:11
@TheConstructor - that is what I was referring to wrt "extra whitespace". You could get around this by writing a function that scans for the first CREATE that is not inside a comment and replaces that. I've don't this/similar in the past but don't have the function's code handy right now to post. Or if you can guarantee none of your object definitions have comments preceding CREATE, ignore the comments issue and just find and replace the first instance of CREATE. — David Spillett, Jul 11 '18 at 10:16
I have tried this approach myself numerous times in the past and on-balance the Generate-Scripts approach was better and is almost always what I use today unless the number of objects to be changed turns out to be relatively small. — RBarryYoung, Jul 11 '18 at 18:23

Sting · Accepted Answer · 2018-07-10 19:05:42Z

I really like David's answer and upvoted that for a programmatic way of doing things.

But you can try this today for a test-run in Azure via SSMS:

Right click your database --> Tasks --> Generate Scripts..

[Back Story] we had a junior DBA who upgraded all our test environments to SQL 2008 R2 while our production environments were at SQL 2008. It's a change that makes me cringe to this day. To migrate to production, from test, we had to generate scripts within SQL, using generate scripts, and in the advanced options we used the 'Type of Data to script: Schema and Data' option to generate a massive text file. We were successfully able to move our test R2 databases to our legacy SQL 2008 servers -- where a database restore to a lower version would not have worked. We used sqlcmd to input the large file--as the files were often too big for the SSMS text buffer.

What I'm saying here is that this option would probably work for you as well. You'll just need to do one additional step and search and replace getdate() with [dbo].getlocaldate in the generated text file. (I would put your function into the database before migration though).

(I never wanted to be proficient at this band-aid of a database restore, but for a while it became a defacto way of doing things. And, it worked every time.)

If you choose this route, be sure and select the Advanced button and select all the options you need (read each one) to move from the old database to the new database--like the defaults you mentioned. But give it a few test runs in Azure. I bet you'll find that this is one solution that works -- with a modicum of effort.

KumarHarsh · Accepted Answer · 2018-07-13 11:31:05Z

Dynamically alter all proc and udf to change value

    DECLARE @Text   NVARCHAR(max), 
        @spname NVARCHAR(max), 
        @Type   CHAR(5), 
        @Sql    NVARCHAR(max) 
DECLARE @getobject CURSOR 

SET @getobject = CURSOR 
FOR SELECT sc.text, 
           so.NAME, 
           so.type 
    FROM   sys.syscomments sc 
           INNER JOIN sysobjects so 
                   ON sc.id = so.id 
    WHERE  sc.[text] LIKE '%getdate()%' 

--and type in('P','FN') 
OPEN @getobject 

FETCH next FROM @getobject INTO @Text, @spname, @Type 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF ( @Type = 'P' 
            OR @Type = 'FN' ) 
        SET @Text = Replace(@Text, 'getdate', 'dbo.getlocaldate') 

      SET @Text = Replace(@Text, 'create', 'alter') 

      EXECUTE Sp_executesql 
        @Text 

      PRINT @Text 

      --,@spname,@Type 
      FETCH next FROM @getobject INTO @Text, @spname, @Type 
  END 

CLOSE @getobject 

DEALLOCATE @getobject

    CREATE PROCEDURE [dbo].[Testproc1] 
AS 
    SET nocount ON; 

  BEGIN 
      DECLARE @CurDate DATETIME = Getdate() 
  END

Notice commented sysobjects Type column condition.My script will alter only proc and UDF.

This script will alter all Default Constraint which contain GetDate()

    DECLARE @TableName      VARCHAR(300), 
        @constraintName VARCHAR(300), 
        @colName        VARCHAR(300), 
        @Sql            NVARCHAR(max) 
DECLARE @getobject CURSOR 

SET @getobject = CURSOR 
FOR SELECT ds.NAME, 
           sc.NAME AS colName, 
           so.NAME AS Tablename 
    --,ds.definition 
    FROM   sys.default_constraints ds 
           INNER JOIN sys.columns sc 
                   ON ds.object_id = sc.default_object_id 
           INNER JOIN sys.objects so 
                   ON so.object_id = ds.parent_object_id 
    WHERE  definition LIKE '%getdate()%' 

OPEN @getobject 

FETCH next FROM @getobject INTO @constraintName, @colName, @TableName 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @Sql = 'ALTER TABLE ' + @TableName 
                 + ' DROP CONSTRAINT ' + @constraintName + '; ' 
                 + Char(13) + Char(10) + '           ' + Char(13) + Char(10) + '' 
      SET @Sql = @Sql + ' ALTER TABLE ' + @TableName 
                 + ' ADD CONSTRAINT ' + @constraintName 
                 + '          DEFAULT dbo.GetLocaledate() FOR ' 
                 + @colName + ';' + Char(13) + Char(10) + '          ' + Char(13) 
                 + Char(10) + '' 

      PRINT @Sql 

      EXECUTE sys.Sp_executesql 
        @Sql 

      --,@spname,@Type 
      FETCH next FROM @getobject INTO @constraintName, @colName, @TableName 
  END 

CLOSE @getobject 

DEALLOCATE @getobject

Henrik Staun Poulsen · Accepted Answer · 2019-01-24 09:05:14Z

I have upvoted Evan Carrolls answer, as I think this is the best solution. I have not been able to convince my colleagues that they should change a lot of C# code, so I had to use the code that David Spillett wrote. I have fixed a couple of problems with UDFs, Dynamic SQL and Schemas (not all code use "dbo.") like this:

DECLARE C CURSOR LOCAL STATIC FOR
        SELECT sm.definition, so.type
        FROM   sys.objects so
        JOIN   sys.all_sql_modules sm ON sm.object_id = so.object_id
        WHERE  so.type IN ('P', 'V')
        AND CHARINDEX('getdate()', sm.definition) > 0
        ORDER BY so.name

DECLARE @SQL NVARCHAR(MAX), @objtype NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
    FETCH NEXT FROM C INTO @SQL, @objtype
    IF @@FETCH_STATUS <> 0 BREAK

    IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE') 
    IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE   PROCEDURE', 'ALTER PROCEDURE') /* when you write "create or alter proc" */
    IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW'     , 'ALTER VIEW'     ) 
    IF CHARINDEX('getdate())''', @sql) > 0 BEGIN  /* when dynamic SQL is used */
        IF CHARINDEX('utl.getdate())''', @sql) = 0 SET @SQL = REPLACE(@SQL, 'GETDATE()', 'utl.getdate()') 
    end
    ELSE begin
        SET @SQL = REPLACE(@SQL, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset,  SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')') 
    end
    EXEC dbo.LongPrint @String = @sql    
    EXEC (@SQL)
END
CLOSE C
DEALLOCATE C

and the default constraints like this:

DECLARE C CURSOR LOCAL STATIC FOR
        SELECT AlterDefaultSQL = 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'
                               + CHAR(10)
                               + 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset,  SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')')+' FOR '+sc.name+';'
        FROM   sys.tables st
        JOIN   sys.default_constraints si ON si.parent_object_id = st.object_id
        JOIN   sys.columns sc ON sc.default_object_id = si.object_id
        INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
        WHERE CHARINDEX('getdate()', si.definition) > 0
        ORDER BY st.name, sc.name

DECLARE @SQL NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
    FETCH NEXT FROM C INTO @SQL
    IF @@FETCH_STATUS <> 0 BREAK

    EXEC dbo.LongPrint @String = @sql  
    EXEC (@SQL)
    FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C

UDFs
The suggestion to use a UDF that returns todays date and time looks nice, but I think there are still enough performance problems with UDFs, so I've chosen to use the very long and ugly AT TIME ZONE solution.

Stack Exchange Network

current community

your communities

more stack exchange communities

Changing the use of GETDATE() in the entire database

6 Answers 6

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server azure-sql-database sql-server-2017 or ask your own question.

Hot Network Questions

Changing the use of GETDATE() in the entire database

6 Answers 6

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server azure-sql-database sql-server-2017 or ask your own question.

Related

Hot Network Questions