We typically use the GETDATE() function to fetch the current DateTime when inserting/updating statements or set a default constraint for a DateTime column when creating a table.
Insert statements:
INSERT INTO <table_name> (ID, <column1>, <column2>, Date)
VALUES (1, <value1>, <value2>, GETDATE())
Update statements:
UPDATE <table_name> SET Date = GETDATE() WHERE <column1> = <value1>
Set a default constraint:
CREATE TABLE <table_name> (ID int Primary key,
<column1> <Date_Type1>,
<column2> <Date_Type2>,
Date Datetime default GETDATE());
Since the SQL server's default timezone is Pacific Standard Time (PST), every time you use the above DML statements, the SQL server will insert the PST DateTime in the respective rows. You cannot change the server's default timezone directly, however, you can convert the timezone to a local timezone using AT TIME ZONE function that converts an inputdate to the corresponding datetimeoffset value in the target time zone.
Let’s walk through an example.
Connect to your MSSQL created in your hosting account from your local SSMS, check the present timezone.
DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone AS 'Present server TimeZone'
Confirm it is set to PST.
Create an "Orders" table with a DateTime column in your database
CREATE TABLE [Orders](
[id] [int] PRIMARY KEY NOT NULL,
[product] [nvarchar](250) NULL,
[OrderTime] [datetime] NULL,
)
Insert sample date into this table
INSERT INTO [Orders] VALUES (1, 'P1', GETDATE())
INSERT INTO [Orders] VALUES (2, 'P2', GETDATE())
INSERT INTO [Orders] VALUES (3, 'P3', GETDATE())
Select the table to check stored data and timezone
SELECT * FROM [Orders]
In order to convert the DateTime to local timezone
(in this example, I will convert the server time zone to India Standard Time (IST). If you want to convert to another timezone, just replace the 'India Standard Time' flag with your timezone name. You can get the timezone info list through the below statements or the last part of this article),
SELECT * FROM sys.time_zone_info
use AT TIME ZONE function, see the following:
SELECT id, product, OrderTime, OrderTime AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'India Standard Time' as OrderTimeIST FROM [Orders]
if your "OrderTime" is not a strict DateTime type, you will want to use the CONVERT() function to format the type first, i.e.
SELECT id, product, OrderTime, CONVERT(datetime2, OrderTime) AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'India Standard Time' as OrderTimeIST FROM [Orders]
Confirm the difference between "OrderTime" and "OrderTimeIST" above.
This approach works well if you create a new database and all data is inserted from the server with server timezone. If you restore the database with a local backup that contains your local time, any new data inserted in the database will default to the PST timezone, resulting in data inconsistency issues. To overcome this problem, you can create a function that returns the local time.
CREATE FUNCTION convert_to_localtime(@PST_Time datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @result DATETIME = NULL;
SELECT @result = @PST_Time AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'India Standard Time';
RETURN @result
END
Select statements:
SELECT id, product, OrderTime, [dbo].[convert_to_localtime](OrderTime) as OrderTimeIST FROM [Orders]
Insert statements:
INSERT INTO [Orders] VALUES (4, 'P4', [dbo].[convert_to_localtime](GETDATE()))
Similar usage for update and constraint statements.
Confirm the results
TimeZone info list:
name | current_utc_offset | is_currently_dst
--------------------------------+--------------------+------------------
Dateline Standard Time | -12:00 | 0
UTC-11 | -11:00 | 0
Aleutian Standard Time | -10:00 | 1
Hawaiian Standard Time | -10:00 | 0
Marquesas Standard Time | -09:30 | 0
Alaskan Standard Time | -09:00 | 1
UTC-09 | -09:00 | 0
Pacific Standard Time (Mexico) | -08:00 | 1
UTC-08 | -08:00 | 0
Pacific Standard Time | -08:00 | 1
US Mountain Standard Time | -07:00 | 0
Mountain Standard Time (Mexico) | -07:00 | 1
Mountain Standard Time | -07:00 | 1
Central America Standard Time | -06:00 | 0
Central Standard Time | -06:00 | 0
Easter Island Standard Time | -05:00 | 1
Central Standard Time (Mexico) | -06:00 | 0
Canada Central Standard Time | -06:00 | 1
SA Pacific Standard Time | -05:00 | 0
Eastern Standard Time (Mexico) | -05:00 | 0
Eastern Standard Time | -05:00 | 0
Haiti Standard Time | -05:00 | 1
Cuba Standard Time | -05:00 | 1
US Eastern Standard Time | -05:00 | 1
Turks And Caicos Standard Time | -05:00 | 1
Paraguay Standard Time | -03:00 | 0
Atlantic Standard Time | -04:00 | 1
Venezuela Standard Time | -04:00 | 0
Central Brazilian Standard Time | -03:00 | 0
SA Western Standard Time | -04:00 | 0
Pacific SA Standard Time | -03:00 | 0
Newfoundland Standard Time | -03:30 | 1
Tocantins Standard Time | -03:00 | 0
E. South America Standard Time | -02:00 | 0
SA Eastern Standard Time | -03:00 | 0
Argentina Standard Time | -03:00 | 0
Greenland Standard Time | -03:00 | 1
Montevideo Standard Time | -03:00 | 0
Magallanes Standard Time | -03:00 | 0
Saint Pierre Standard Time | -03:00 | 1
Bahia Standard Time | -03:00 | 0
UTC-02 | -02:00 | 0
Mid-Atlantic Standard Time | -02:00 | 1
Azores Standard Time | -01:00 | 1
Cape Verde Standard Time | -01:00 | 0
UTC | +00:00 | 0
GMT Standard Time | +00:00 | 1
Greenwich Standard Time | +00:00 | 0
W. Europe Standard Time | +01:00 | 0
Central Europe Standard Time | +01:00 | 1
Romance Standard Time | +01:00 | 1
Morocco Standard Time | +01:00 | 1
Sao Tome Standard Time | +01:00 | 1
Central European Standard Time | +01:00 | 1
W. Central Africa Standard Time | +01:00 | 0
Jordan Standard Time | +02:00 | 1
GTB Standard Time | +02:00 | 1
Middle East Standard Time | +02:00 | 1
Egypt Standard Time | +02:00 | 0
E. Europe Standard Time | +02:00 | 1
Syria Standard Time | +02:00 | 1
West Bank Standard Time | +02:00 | 1
South Africa Standard Time | +02:00 | 0
FLE Standard Time | +02:00 | 1
Israel Standard Time | +02:00 | 1
Kaliningrad Standard Time | +02:00 | 0
Sudan Standard Time | +02:00 | 0
Libya Standard Time | +02:00 | 0
Namibia Standard Time | +02:00 | 0
Arabic Standard Time | +03:00 | 0
Turkey Standard Time | +03:00 | 0
Arab Standard Time | +03:00 | 0
Belarus Standard Time | +03:00 | 0
Russian Standard Time | +03:00 | 0
E. Africa Standard Time | +03:00 | 0
Iran Standard Time | +03:30 | 0
Arabian Standard Time | +04:00 | 1
Astrakhan Standard Time | +04:00 | 0
Azerbaijan Standard Time | +04:00 | 0
Russia Time Zone 3 | +04:00 | 0
Mauritius Standard Time | +04:00 | 0
Saratov Standard Time | +04:00 | 0
Georgian Standard Time | +04:00 | 0
Volgograd Standard Time | +04:00 | 0
Caucasus Standard Time | +04:00 | 0
Afghanistan Standard Time | +04:30 | 0
West Asia Standard Time | +05:00 | 0
Ekaterinburg Standard Time | +05:00 | 0
Pakistan Standard Time | +05:00 | 0
India Standard Time | +05:30 | 0
Sri Lanka Standard Time | +05:30 | 0
Nepal Standard Time | +05:45 | 0
Central Asia Standard Time | +06:00 | 0
Bangladesh Standard Time | +06:00 | 0
Omsk Standard Time | +06:00 | 0
Myanmar Standard Time | +06:30 | 0
SE Asia Standard Time | +07:00 | 0
Altai Standard Time | +07:00 | 0
W. Mongolia Standard Time | +07:00 | 0
North Asia Standard Time | +07:00 | 0
N. Central Asia Standard Time | +07:00 | 0
Tomsk Standard Time | +07:00 | 0
China Standard Time | +08:00 | 0
North Asia East Standard Time | +08:00 | 0
Singapore Standard Time | +08:00 | 0
W. Australia Standard Time | +08:00 | 0
Taipei Standard Time | +08:00 | 0
Ulaanbaatar Standard Time | +08:00 | 0
Aus Central W. Standard Time | +08:45 | 0
Transbaikal Standard Time | +09:00 | 0
Tokyo Standard Time | +09:00 | 0
North Korea Standard Time | +09:00 | 0
Korea Standard Time | +09:00 | 0
Yakutsk Standard Time | +09:00 | 0
Cen. Australia Standard Time | +10:30 | 0
AUS Central Standard Time | +09:30 | 0
E. Australia Standard Time | +10:00 | 0
AUS Eastern Standard Time | +11:00 | 0
West Pacific Standard Time | +10:00 | 0
Tasmania Standard Time | +11:00 | 0
Vladivostok Standard Time | +10:00 | 0
Lord Howe Standard Time | +11:00 | 0
Bougainville Standard Time | +11:00 | 0
Russia Time Zone 10 | +11:00 | 0
Magadan Standard Time | +11:00 | 0
Norfolk Standard Time | +11:00 | 0
Sakhalin Standard Time | +11:00 | 0
Central Pacific Standard Time | +11:00 | 0
Russia Time Zone 11 | +12:00 | 0
New Zealand Standard Time | +13:00 | 0
UTC+12 | +12:00 | 0
Fiji Standard Time | +12:00 | 0
Kamchatka Standard Time | +12:00 | 1
Chatham Islands Standard Time | +13:45 | 0
UTC+13 | +13:00 | 0
Tonga Standard Time | +13:00 | 0
Samoa Standard Time | +14:00 | 0
Line Islands Standard Time | +14:00 | 0
Article ID: 2230, Created: August 26, 2022 at 1:43 AM, Modified: August 26, 2022 at 4:28 AM