Getdate() function to get date for my timezone

sql-server getdate
loveprogramming picture loveprogramming · Nov 20, 2013 · Viewed 45.7k times · Source

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.

Answer

Don Cheadle picture Don Cheadle · Apr 24, 2019 
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.

Related questions

How to print GETDATE() in SQL Server with milliseconds in time?

I want to print GETDATE() in SQL Server 2008, I need the time with milliseconds (this is for debugging purpose - to find sp's execution time ) I find this Difference SELECT GETDATE() returns 2011-03-15 18:43:44.100 print GETDATE() returns Mar 15 2011 6:44PM I …

Read More
Rounding SQL DateTime to midnight

I am having a small problem with my SQL query. I'm using the GETDATE function, however, let's say I execute the script at 5PM, it will pull up records between 12/12/2011 5PM to 12/18/2011 5PM. How can I make it pull up …

Read More
Incorrect syntax near ')' calling stored procedure with GETDATE

Maybe I am having a moment of 'afternoon', but can anyone explain why I get Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ')'. When running CREATE PROC DisplayDate (@DateVar DATETIME) AS BEGIN SELECT @DateVar END GO EXEC DisplayDate GETDATE();

Read More