You’ve had your existing application running for years, relying on DEFAULT constraints to set DATETIME fields to the current date using GETDATE(). Everything was great until you migrated to Azure SQL Database – and realized that your SQL Server is now set to UTC, and you can’t change it.
Fortunately, you can change your DEFAULT GETDATE() constraint to save date in your time zone. Just specify the default as shown below, specifying your time zone of choice:
default convert(datetimeoffset, getdate()) at time zone 'Eastern Standard Time'
To prove this, run the following, and note the output will show the time in Eastern Standard Time as opposed to UTC.
create table #test (
val nvarchar(max),
dt datetime not null default convert(datetimeoffset, getdate()) at time zone 'Eastern Standard Time'
)
insert into #test ( val ) values ('yo')
select * from #test
Voila! No more time zone issue.