SQL Nannyteaching engineers the SQL way

Learning wordpress

I’m trying to navigate WordPress as I haven’t messed with websites since college.  I don’t want to discuss how long ago that was.  Regardless, this post is annoying me because when I preview it, my code snippet is squished into a narrow column.  On to fix that issue then maybe I can take you on a journey for why I wrote this script anyway 😑

DECLARE @UTC_date DATETIME = GETUTCDATE();

/****  UTC to SYSTEM datetime ****/
SELECT @UTC_date AS variable_date_value,
       GETUTCDATE() AS utc_date,
       SYSDATETIMEOFFSET() AS system_datetime_offset,										/* have to use the sysdatetime offset to get the offset value from utc */
       DATENAME(TZOFFSET, SYSDATETIMEOFFSET()) AS system_time_zone_offset,					/* final offset value without date, usually -05:00 (or -06:00) for CST */
       CONVERT(DATETIMEOFFSET, @UTC_date) AS variable_datetime_offset,						/* offset is 00:00 because it is UTC */
       DATENAME(TZOFFSET, CONVERT(DATETIMEOFFSET, @UTC_date)) AS variable_time_zone_offset, /* adding to previous line - 00:00 because UTC */
	   SWITCHOFFSET(CONVERT(DATETIMEOFFSET, @UTC_date), DATENAME(TZOFFSET, SYSDATETIMEOFFSET())) utc_to_systime_offset,
/*  this is the END RESULT with the final conversion from UTC to system time (not local, the time zone of the machine) most if not all at CSI are CST */
       CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, @UTC_date), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS utc_to_system_time;

/****************************************************************/
/****  if you want to convert UTC into a specific time zone  ****/
SELECT CONVERT(DATETIME2, @UTC_date, 126) AT TIME ZONE tz.name AS variable_to_tz_offset
	 , CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,@UTC_date),tz.current_utc_offset)) variable_to_tz_datetime
  	 , tz.name AS time_zone_name, tz.current_utc_offset, tz.is_currently_dst
FROM sys.time_zone_info tz;

/*******************************************/
/** example for alaska standard time AKST **/
SELECT  
	 CONVERT(DATETIME2, @UTC_date, 126) AT TIME ZONE 'Alaskan Standard Time' AS UTC_variable_to_AKST
	,DATENAME(TZOFFSET,CONVERT(DATETIME2, @UTC_date, 126) AT TIME ZONE 'Alaskan Standard Time') AS AKST_UTC_OFFSET
	,CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, @UTC_date), DATENAME(TZOFFSET,CONVERT(DATETIME2, @UTC_date, 126) AT TIME ZONE 'Alaskan Standard Time'))) AS UTC_to_AKST_formatted

/*converting SYSTEM time to another time zone*/
DECLARE @local_datetime DATETIMEOFFSET = SYSDATETIMEOFFSET()
SELECT  
	DATENAME(TZOFFSET, @local_datetime) system_tz_offset,
	DATENAME(TZOFFSET,CONVERT(DATETIME2, @UTC_date, 126) AT TIME ZONE 'Alaskan Standard Time') AS AKST_tzone_offset,
	SWITCHOFFSET(@local_datetime, DATENAME(TZOFFSET,CONVERT(DATETIME2, @UTC_date, 126) AT TIME ZONE 'Alaskan Standard Time')) AS AKST_datetime_offset,
	CONVERT(DATETIME,SWITCHOFFSET(@local_datetime,DATENAME(TZOFFSET,CONVERT(DATETIME2, @UTC_date, 126) AT TIME ZONE 'Alaskan Standard Time'))) AS AKST_datetime