sql-servergetdate

Getdate() function to get date for my timezone

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.

Solution

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