# script: scripts/01_function_surface.sql | database: lab-utc | started (UTC): 2026-10-16 01:49:38
/* 01 - Function surface (run on BOTH databases; run on CST before AND after the time zone change)
   Goal: which date/time functions follow the effective time zone and which stay UTC.
   Expected per the article: GETDATE, SYSDATETIME, CURRENT_TIMESTAMP, CURRENT_DATE, SYSDATETIMEOFFSET,
   CURRENT_TIMEZONE, CURRENT_TIMEZONE_ID follow the setting; GETUTCDATE and SYSUTCDATETIME never do. */
SET NOCOUNT ON;
PRINT '=== 01 Function surface on ' + DB_NAME();
=== 01 Function surface on lab-utc
SELECT DB_NAME() AS db,
       (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') AS db_tz_config,
       CURRENT_TIMEZONE_ID() AS tz_id,
       CURRENT_TIMEZONE() AS tz_display;
db|db_tz_config|tz_id|tz_display
--|------------|-----|----------
lab-utc|LOCAL|UTC|(UTC) Coordinated Universal Time

SELECT 'GETDATE()' AS fn, CONVERT(varchar(40), GETDATE(), 121) AS value UNION ALL
SELECT 'SYSDATETIME()', CONVERT(varchar(40), SYSDATETIME(), 121) UNION ALL
SELECT 'CURRENT_TIMESTAMP', CONVERT(varchar(40), CURRENT_TIMESTAMP, 121) UNION ALL
SELECT 'SYSDATETIMEOFFSET()', CONVERT(varchar(40), SYSDATETIMEOFFSET(), 121) UNION ALL
SELECT 'GETUTCDATE()', CONVERT(varchar(40), GETUTCDATE(), 121) UNION ALL
SELECT 'SYSUTCDATETIME()', CONVERT(varchar(40), SYSUTCDATETIME(), 121);
fn|value
--|-----
GETDATE()|2026-10-16 01:49:39.460
SYSDATETIME()|2026-10-16 01:49:39.4639450
CURRENT_TIMESTAMP|2026-10-16 01:49:39.460
SYSDATETIMEOFFSET()|2026-10-16 01:49:39.4639450 +00:00
GETUTCDATE()|2026-10-16 01:49:39.463
SYSUTCDATETIME()|2026-10-16 01:49:39.4639450

PRINT '--- CURRENT_DATE (separate batch in case it is not supported)';
SELECT 'CURRENT_DATE' AS fn, CONVERT(varchar(40), CURRENT_DATE, 121) AS value,
       CONVERT(varchar(10), GETUTCDATE(), 121) AS utc_date;
--- CURRENT_DATE (separate batch in case it is not supported)
fn|value|utc_date
--|-----|--------
CURRENT_DATE|2026-10-16|2026-10-16

PRINT '--- Offsets: local minus UTC';
SELECT DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) AS getdate_minus_getutcdate_min,
       DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME()) AS sysdatetime_minus_sysutcdatetime_min,
       DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) AS sysdatetimeoffset_tzoffset_min;
--- Offsets: local minus UTC
getdate_minus_getutcdate_min|sysdatetime_minus_sysutcdatetime_min|sysdatetimeoffset_tzoffset_min
----------------------------|------------------------------------|------------------------------
0|0|0

# sqlcmd exit code: 0