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.

Don Cheadle · Accepted Answer · 2019-09-13 15:10:52Z

47

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.

edited Sep 13, 2019 at 15:10

answered Apr 24, 2019 at 15:03

Don Cheadle

5,6155 gold badges41 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ejcortes Over a year ago

For Azure, this is the way to go.

Don Cheadle Over a year ago

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

Imran Rizvi Over a year ago

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

Sylvain Gantois Over a year ago

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.

Sylvain Gantois Over a year ago

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

|

Szymon · Accepted Answer · 2013-11-20 04:02:26Z

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.

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

Mario Levrero · Accepted Answer · 2018-08-22 07:55:11Z

11

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

edited Aug 22, 2018 at 7:55

answered Jan 15, 2018 at 15:16

Mario Levrero

3,3874 gold badges28 silver badges60 bronze badges

2 Comments

Don Cheadle Over a year ago

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.

Simon_Weaver Over a year ago

And yes you do have to use the full name, not abbreviations such as CST or CEST. Presumably this is because they could quickly get ambiguous across continents! Use select * from sys.time_zone_info to get a list of what's valid.

Aaron Bertrand · Accepted Answer · 2021-12-28 03:22:29Z

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:

Kelmen · Accepted Answer · 2013-11-20 02:39:32Z

0

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.

answered Nov 20, 2013 at 2:39

Kelmen

1,0631 gold badge11 silver badges24 bronze badges

1 Comment

loveprogramming Over a year ago

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.

Shiwani · Accepted Answer · 2023-02-27 05:58:46Z

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 )

cddt · Accepted Answer · 2025-09-15 22:00:01Z

To expand on the top answer (rejected edit):

Valid time zones are exposed through sys.time_zone_info. This is helpful if you don't know the official name of a particular time zone.

Collectives™ on Stack Overflow

Getdate() function to get date for my timezone

7 Answers 7

6 Comments

3 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions