Zen of SQL: New Date and Time Data Types

-- The range for the DATE datatype is from 0001-01-01 through 9999-12-31

DECLARE @date DATE;

SET @date = CURRENT_TIMESTAMP;

PRINT @date;

-- The range for the TIME data type is 00:00:00.0000000 through 23:59:59.9999999

DECLARE @time TIME;

SET @time = CURRENT_TIMESTAMP;

PRINT @time;

-- Fraction 0 through 7

DECLARE @datetime2 DATETIME2(7);

SET @datetime2 = CURRENT_TIMESTAMP;

PRINT @datetime2;

SET @datetime2 = SYSDATETIME();

PRINT @datetime2;

-- Timezone offset range: -14:00 through +14:00

-- Date range: 0001-01-01 through 9999-12-31

-- Time range: 00:00:00 through 23:59:59.9999999

DECLARE @datetimeoffset DATETIMEOFFSET(7);

SET @datetimeoffset = CURRENT_TIMESTAMP;

PRINT @datetimeoffset;

SET @datetimeoffset = SYSDATETIMEOFFSET();

PRINT @datetimeoffset;

-- Functions

SELECT SYSDATETIME(),

SYSDATETIMEOFFSET(),

SYSUTCDATETIME(),

CURRENT_TIMESTAMP,

GETDATE(),

GETUTCDATE();

-- New ISO week option for week number calculation

SELECT DATEPART(ISO_WEEK, CURRENT_TIMESTAMP);

-- Switch between time zones

SELECT SYSDATETIMEOFFSET(),

SWITCHOFFSET(SYSDATETIMEOFFSET(), '+06:00'); --Paris

-- Set date and time offset based on DATETIME value

SELECT CURRENT_TIMESTAMP,

TODATETIMEOFFSET(CURRENT_TIMESTAMP, '-01:00');

-- Convert DATETIME to DATE and TIME

SELECT CONVERT(DATE, CURRENT_TIMESTAMP),

CONVERT(TIME, CURRENT_TIMESTAMP);