Post
Ask Question

Getting Particular Timezones Current Date Time in SQL Server

To Get Particular time zones' current date time, we can use the below query

SELECT *
FROM sys.time_zone_info

The Above query will show the time zones supported by sql server.

Query result

name current_utc_offset is_currently_dst
Dateline Standard Time -12:00 0
UTC-11 -11:00 0
Aleutian Standard Time -09:00 1
Hawaiian Standard Time -10:00 0
Marquesas Standard Time #SPILL! 0
Alaskan Standard Time -08:00 1
UTC-09 -09:00 0
Pacific Standard Time (Mexico) -07:00 1
UTC-08 -08:00 0
Pacific Standard Time -07:00 1
US Mountain Standard Time -07:00 0
Mountain Standard Time (Mexico) -07:00 0
Mountain Standard Time -06:00 1
Yukon Standard Time -07:00 0
Central America Standard Time -06:00 0
Central Standard Time -05:00 1
Easter Island Standard Time -06:00 0
Central Standard Time (Mexico) -06:00 0
Canada Central Standard Time -06:00 0
SA Pacific Standard Time -05:00 0
Eastern Standard Time (Mexico) -05:00 0
Eastern Standard Time -04:00 1
Haiti Standard Time -04:00 1
Cuba Standard Time -04:00 1
US Eastern Standard Time -04:00 1
Turks And Caicos Standard Time -04:00 1
Paraguay Standard Time -04:00 0
Atlantic Standard Time -03:00 1
Venezuela Standard Time -04:00 0
Central Brazilian Standard Time -04:00 0
SA Western Standard Time -04:00 0
Pacific SA Standard Time -04:00 0
Newfoundland Standard Time #SPILL! 1
Tocantins Standard Time -03:00 0
E. South America Standard Time -03:00 0
SA Eastern Standard Time -03:00 0
Argentina Standard Time -03:00 0
Montevideo Standard Time -03:00 0
Magallanes Standard Time -03:00 0
Saint Pierre Standard Time -02:00 1
Bahia Standard Time -03:00 0
UTC-02 -02:00 0
Greenland Standard Time -01:00 1
Mid-Atlantic Standard Time -01:00 1
Azores Standard Time +00:00 1
Cape Verde Standard Time -01:00 0
UTC +00:00 0
GMT Standard Time +01:00 1
Greenwich Standard Time +00:00 0
Sao Tome Standard Time +00:00 0
Morocco Standard Time +01:00 1
W. Europe Standard Time +02:00 1
Central Europe Standard Time +02:00 1
Romance Standard Time +02:00 1
Central European Standard Time +02:00 1
W. Central Africa Standard Time +01:00 0
GTB Standard Time +03:00 1
Middle East Standard Time +03:00 1
Egypt Standard Time +03:00 1
E. Europe Standard Time +03:00 1
West Bank Standard Time +03:00 1
South Africa Standard Time +02:00 0
FLE Standard Time +03:00 1
Israel Standard Time +03:00 1
South Sudan Standard Time +02:00 0
Kaliningrad Standard Time +02:00 0
Sudan Standard Time +02:00 0
Libya Standard Time +02:00 0
Namibia Standard Time +02:00 0
Jordan Standard Time +03:00 0
Arabic Standard Time +03:00 0
Syria 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
Volgograd Standard Time +03:00 0
Iran Standard Time +03:30 0
Arabian Standard Time +04:00 0
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
Caucasus Standard Time +04:00 0
Afghanistan Standard Time +04:30 0
West Asia Standard Time +05:00 0
Qyzylorda 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 +09:30 0
AUS Central Standard Time +09:30 0
E. Australia Standard Time +10:00 0
AUS Eastern Standard Time +10:00 0
West Pacific Standard Time +10:00 0
Tasmania Standard Time +10:00 0
Vladivostok Standard Time +10:00 0
Lord Howe Standard Time +10:30 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 +12:00 0
UTC+12 +12:00 0
Fiji Standard Time +12:00 0
Kamchatka Standard Time +13:00 1
Chatham Islands Standard Time +12:45 0
UTC+13 +13:00 0
Tonga Standard Time +13:00 0
Samoa Standard Time +13:00 0
Line Islands Standard Time +14:00 0
Eastern European Standard Time +02:00 1
W. Central Africa Standard Time +01:00 0
GTB Standard Time +03:00 1
Middle East Standard Time +03:00 1
Egypt Standard Time +03:00 1
E. Europe Standard Time +03:00 1
West Bank Standard Time +03:00 1
South Africa Standard Time +02:00 0
FLE Standard Time +03:00 1
Israel Standard Time +03:00 1
South Sudan Standard Time +02:00 0
Kaliningrad Standard Time +02:00 0
Sudan Standard Time +02:00 0
Libya Standard Time +02:00 0
Namibia Standard Time +02:00 0
Jordan Standard Time +03:00 0
Arabic Standard Time +03:00 0
Syria 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
Volgograd Standard Time +03:00 0
Iran Standard Time +03:30 0
Arabian Standard Time +04:00 0
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
Caucasus Standard Time +04:00 0
Afghanistan Standard Time +04:30 0
West Asia Standard Time +05:00 0
Qyzylorda 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 +09:30 0
AUS Central Standard Time +09:30 0
E. Australia Standard Time +10:00 0
AUS Eastern Standard Time +10:00 0
West Pacific Standard Time +10:00 0
Tasmania Standard Time +10:00 0
Vladivostok Standard Time +10:00 0
Lord Howe Standard Time +10:30 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 +12:00 0
UTC+12 +12:00 0
Fiji Standard Time +12:00 0
Kamchatka Standard Time +13:00 1
Chatham Islands Standard Time +12:45 0
UTC+13 +13:00 0
Tonga Standard Time +13:00 0
Samoa Standard Time +13:00 0
Line Islands Standard Time +14:00 0
Lord Howe Standard Time +10:30 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 +12:00 0
UTC+12 +12:00 0
Fiji Standard Time +12:00 0
Kamchatka Standard Time +13:00 1
Chatham Islands Standard Time +12:45 0
UTC+13 +13:00 0
Tonga Standard Time +13:00 0
Samoa Standard Time +13:00 0
Line Islands Standard Time +14:00 0


Current Datetime of CST Time Zone

SELECT CAST(SYSDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS DATETIME) AS CurrentCentralTime

Query Result

CurrentCentralTime
2024-06-11 08:45:09.323

Current DateTime of CST with timezone offset

SELECT SYSDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS CurrentCentralTime 
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS CurrentCentralTime

Query Result

CurrentCentralTime
2024-06-11 08:46:52.0425022 -05:00

Query Explanation


SYSDATETIME()

  • This function returns the current date and time of the SQL Server system without any time zone information. The result is of type datetime2.

SYSDATETIMEOFFSET()

  • This function returns the current system date and time with the time zone offset of the system where the SQL Server is running.
  • Example output: 2024-06-11 14:23:45.1234567 +05:30

CAST(... AS DATETIME)

  • This casts the resulting datetimeoffset value to a datetime value, which strips off the time zone offset information. The resulting value is a datetime in Central Standard Time without any offset.

AT TIME ZONE 'UTC'

  • This part converts the datetime value returned by SYSDATETIMEOFFSET() to UTC (Coordinated Universal Time).
  • The AT TIME ZONE clause adjusts the datetime value according to the specified time zone.
  • Example conversion: 2024-06-11 08:53:45.1234567 +00:00 (assuming the original time zone was UTC+5:30).

AT TIME ZONE 'Central Standard Time'

  • This further converts the UTC datetime value to Central Standard Time (CST).
  • CST is UTC-6:00, but it also accounts for Daylight Saving Time (CDT, UTC-5:00) if applicable.
  • Example conversion: 2024-06-11 03:53:45.1234567 -06:00 (for CST).

AS CurrentCentralTime

  • This gives the result a meaningful alias name, CurrentCentralTime.


Similar Articles