Getdate() function to get date for my timezone

Question

I would love to insert a default value into a column with data type datetime2(7). However, because my website is hosted on a server in a different timezone, the getdate function doesn't work properly. I wonder if there is a solution to this. I have done some research and found two ways. First is to use GetUTCDate() function. However, I would need to do the conversion when I display the information. I am sure my web application is used for only my timezone. So I would like to avoid this. Second way, this is the closest I could get this done by using SwitchOffSet function:

CREATE TABLE [dbo].[Test_Date](
[test_id] [int] NOT NULL,
[test_date] [datetime2](7) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Test_Date] ADD  CONSTRAINT [DF_Test_Date_test_date]  DEFAULT (switchoffset(CONVERT([datetimeoffset],getutcdate()),'+13:00')) FOR [test_date]
GO

However, my problem is the +13:00 cause in the next few months, it will be +12:00 cause of the day light saving time change. As a result, I would need to change it every time. Anybody has a solution to this?

Thanks.

Salliesallow

Answer 1

Just store the data in UTC, and use a calendar table to calculate offsets when you read the data (see these tips: part 1, part 2, part 3). Related Q & A:

• GET UTC Date of a past date
• Daylight saving time and time zone best practices
• Best Practices working with Datetimeoffset
• how to convert all datetime columns in a sql server 2005 express database with data to UTC
• Where to set a UTC datetime value in n-tier application: Presentation Layer, Domain, or Database?
• How do I handle the timezones for every Chat message

Virginavirginal

Answer 2

SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

You need the first 'AT TIME ZONE UTC' in order to tell the DB what the value currently is in, so it knows how to get to the second given time zone, 'Central Standard Time' in my example.

Anguish

For Azure, this is the way to go. – Diseased

@Diseased yep I was in Azure at the time I wrote this answer, so it was from the Azure SQL Server perspective. – Anguish

Why can't Azure intelligent enough to convert getdate() to CST? why conversion to UTC required first? – Stormystorting

the first part will return the current date of the server's timezone and consider its the UTC time. In Japan, at 9:51, the first part returns 2022-06-15 09:51:31.420 +00:00 which is obviously wrong. It would work if we know the server's timezone and replace UTC by it. – Jolinejoliotcurie

By 'first part' I meant: SELECT GETDATE() AT TIME ZONE 'UTC' – Jolinejoliotcurie

Answer 3

You can use SYSDATETIMEOFFSET function

select SYSDATETIMEOFFSET()

MSDN description:

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

More on MSDN.

---

Based on clarification in the comment below:

Because you want to store the local time of the client, SQL Server has no way of knowing what is your local time. The best option that would work best would be to send the current time from the client each time.

Catanddog

sysdatetimeoffset() doesn't solve my problem. It will tell me what the time offset in my column compared to UTC. Something like 2007-04-30 13:10:02.0474381 -07:00. It's not what I am want to achieve. I would like to insert the date and time for my timezone. Thanks – Salliesallow

Actually, I do know the location of my clients and it will always be in my timezone. My web application doesn't allow any registration from outside. Thanks – Salliesallow

Answer 4

Since Sql Server 2016 you can use AT TIME ZONE...

SELECT CONVERT(datetime2(0), '2015-03-29T01:01:00', 126)     
AT TIME ZONE 'Central European Standard Time';  

... as specified in the documentation

Unwitting

but more practically, you're converting a DATETIME value... so you need to give the "UTC" timezone of the currently value first, so it knows how to get to the next. – Anguish

Answer 5

since the db timezone info is different with your web server, its best you explicitly pass your desired datetime value from your web app to the db, instead of using db server-side default function.

Junejuneau

This is the most obvious way. But I tried to avoid this if possible. Already written so many stored procedures without the datetime parameter. Thanks. – Salliesallow

Answer 6

Getdate() function to get date for Japan Standard Time Zone[JST]

SELECT CAST( GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo' AS Date )

Hylo