Building Dates

Building a Date

SELECT
    GETDATE() AS DateTime_LTz,
    GETUTCDATE() AS DateTime_UTC;

SELECT
    SYSDATETIME() AS DateTime2_LTz,
    SYSUTCDATETIME() AS DateTime2_UTC;

Breaking Down A Date

DECLARE
    @SomeDate DATETIME(3) = '2019-03-01 08:17:19.332';

SELECT YEAR(@SomeDate);

SELECT MONTH(@SomeDate);

SELECT DAY(@SomeDate);

The above SQL query returns YEAR=20139, MONTH=3, and DAY=1.

Parsing Dates with Date Parts

Parts

• Year / Month / Day
• Day of year
• Day of week
• Week of year
• ISO week of year
• Minute / Second
• Millisecond / Nanosecond

Functions

• DATEPART()

  SELECT
    DATEPART(YEAR, @dt) AS TheYear;
  

• DATENAME()

  SELECT
    DATENAME(MONTH, @dt) AS TheMonth;
  

Adding and Subtracting Dates

DECLARE
    @SomeTime DATETIME2(7) = '1992-07-14 14:49:36.2294852';

SELECT 
    DATEADD(DAY, 1, @SomeTime) AS NextDay,
    DATEADD(Day, -1, @SomeTime) AS PriorDay;

SELECT 
    DATEADD(HOUR, -3, DATEADD(DAY,-4, @SomeTime)) AS Minus4Day3Hours;

Comparing Dates

DECLARE
    @StartTime DATETIME2(7) = '2012-03-01 14:29:36',
    @EndTime DATETIME2(7) = '2012-03-01 18:00:00';

SELECT
    DATEDIFF(SECOND, @StartTime, @EndTime) AS SecondsElapsed,
    DATEDIFF(MINUTE, @StartTime, @EndTIme) AS MinutesElapsed,
    DATEDIFF(HOUR, @StartTime, @EndTime) AS HoursElapsed;

Formatting Dates For Reporting

Formatting Functions

• CAST()
• CONVERT()
• FORMAT()

The CAST() Function

• Useful for converting one data type to another data type, including date types
• No control over formatting dates to strings
• ANSI SQL Standard, meaning any relational and most non-relational databases have this function

Example:

DECLARE
    @SomeDate DATETIME2(3) = '1991-06-04 08:00:09',
    @SomeString NVARCHAR(30) = '1991-06-04 08:00:09',
    @OldDateTime DATETIME = '1991-06-04 08:00:09';

SELECT
    CAST(@SomeDate AS NVARCHAR(30)) AS DateToString,
    CAST(@SomeString AS DATETIME2(3)) AS StringToDate,
    CAST(@OldDateTime AS NVARCHAR(30)) AS OldDateToString;

The CONVERT() Function

• Useful for converting one data type to another data type, including date types
• Some control over formatting from dates to strings using the style parameter
• Specific to T-SQL

Example:

DECLARE 
    @SomeDate DATETIME2(3) = '1793-02-21 11:13:19.033';

SELECT
    CONVERT(NVARCHAR(30), @SomeDate,0) AS DefaultForm,
    CONVERT(NVARCHAR(30), @SomeDate,1) AS UD_mdy,
    CONVERT(NVARCHAR(30), @SomeDate, 101) AS US_mdyyyy,
    CONVERT(NVARCHAR(30), @SomeDate, 120) AS ODBC_sec;

• Sample CONVERT() styles

The FORMAT() Function

• Useful for formatting a date or number in a particular way for reporting
• Much more flexibility over formatting from dates to strings than either CAST() or CONVERT()
• Specific to T-SQL
• Can be slower as you process more rows

DECLARE 
    @SomeDate DATETIME2(3)  = '1793-02-21 11:13:19.033';

SELECT
    FORMAT(@SomeDate, 'd', 'en-US') AS US_d,
    FORMAT(@SomeDate, 'd', 'de-DE') AS DE_d,
    FORMAT(@SomeDate, 'D', 'de-DE') AS DE_D,
    FORMAT(@SomeDate, 'yyyy-MM-dd') AS yMd,

Working with Calendar Tables

A calendar table is a permanent table containing a list of dates and various components of those dates.

SELECT *
FROM dbo.Calendar;

Contents of a Calendar Table

Building a Calendar Table

CREATE TABLE dbo.Calendar (
    DateKey INT NOT NULL,
    [Date] DATE NOT NULL,
    [Day] TINYINT NOT NULL,
    DayOfWeek TINYINT NOT NULL,
    DayName VARCHAR(10) NOT NULL,
    ....
)

SELECT
    CAST(D.DateKey AS INT) AS DateKey,
    D.[DATE] AS [DATE],
    CAST(D.[day] AS TINYINT) AS [day],
    CAST(d.[dayofweek] AS TINYINT) AS [DayOfWeek],
    CAST(DATENAME(WEEKDAY, d.[Date]) AS VARCHAR(10)) AS [DayName],
    ....

Using a Calendar Table

SELECT
    c.Date
FROM dbo.Calendar c
WHERE
    c.MonthName = 'April'
    AND c.DayName = 'Saturday'
    AND c.CalendarYear = 2020
ORDER BY 
    c.Date;

A Quick Note on Apply()

SELECT
    FYStart = DATEADD(MONTH, -6,
                DATEADD(YEAR, 
                    DATEDIFF(YEAR, 0,
                        DATEADD(MONTH, 6, d.[date])), 0)),
      
    FiscalDayOfYear = DATEDIFF(DAY,
                        DATEADD(MONTH, -6, 
                            DATEADD(YEAR,
                                DATEDIFF(YEAR, 0,
                                    DATEADD(MONTH, 6, d.[date])), 0)), d.[Date]) + 1,

    FiscalWeekOfYear = DATEDIFF(WEEK, 
                            DATEADD(MONTH, -6,
                                DATEADD(YEAR,
                                    DATEDIFF(YEAR,0,
                                        DATEADD(MONTH,6,d.[date])), 0)), d.[Date]) +1
FROM dbo.Calendar d;

SELECT
    fy.FYStart,
    FiscalDayOfYear = DATEDIFF(DAY, fy.FYStart, d.[Date])+1,
    FiscalWeekOfYear = DATEDIFF(WEEK, fy.FYStart, d.[Date]) +1
FROM dbo.Calendar d
CROSS APPLY
(
    SELECT FYStart = DATEADD(MONTH, -6,
                        DATEADD(YEAR,
                            DATEDIFF(YEAR,0,
                                DATEADD(MONTH, 6, d.[date])),0))
) fy;

Basic Date From Parts

Dates From Parts

• DATEFROMPARTS(year,month,day)
• TIMEFROMPARTS(hour, minute, second, fraction, precision)
• DATETIMEFROMPARTS(year, month, day, hour, minute, seconds,ms)
• DATETIME2FROMPARTS(year, month, day, hour, minute, second, fraction, precision)
• SMALLDATETIMEFROMPARTS(year, month, day, hour, minute)
• DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, second, fraction, hour_offset, minute_offset, precision)

Dates and Times Together

SELECT
  DATETIMEFROMPARTS(1918,11,11,05,45,17,995) AS DT,
  DATETIME2FROMPARTS(1918,11,11,05,45,17,0,0) AS DT20,
  DATETIME2FROMPARTS(1918,11,11,05,45,17,995,3) AS DT23,
  DATETIME2FROMPARTS(1918,11,11,05,45,17,9951234,3) AS DT27;

Working With Offsets

SELECT
  DATETIMEOFFSETFROMPARTS(2009,08,14,21,00,00,0,5,30,0) AS IST,
  DATETIMEOFFSETFROMPARTS(2009,08,14,21,00,00,0,5,30,0) AT TIME ZONE 'UTC' AS UTC;

Translating Date Strings

Casting Strings

SELECT
    CAST('09/14/99' AS DATE) AS USDate;

Converting Strings

SELECT
    CONVERT(DATETIME2(3), 'April 4, 2019 11:52:29.998 PM') AS April14;

Parsing Strings

SELECT
    PARSE('25 Dezember 2014' AS DATE USING 'de-de') AS Wihnachten;

The Cost Of Parsing

| --- | --- |

Setting Languages

SET LANGUAGE 'FRENCH'
DECLARE
    @FrenchDate NVARCHAR(30) = N'18 avril 2019',
    @FrenchNumberDate NVARCHAR(30) = N'18/4/2019';

SELECT
    CAST(@FrenchDate AS DATETIME),
    CAST(@FrenchNumberDate AS DATETIME);

Working With Offsets

Anatomy of a DATETIMEOFFSET Components

| --- | --- |

Changing Offsets

DECLARE @SomeDate DATETIMEOFFSET = '2019-04-10 12:59:02.3908505 -04:00';

SELECT
    SWITCHOFFSET(@SomeDate, '-07:00') AS LATime;

Converting to DATETIMEOFFSET

DECLARE @SomeDate DATETIME2(3) = '2019-04-10 12:59:02.390';

SELECT
    TODATETIMEOFFSET(@SomeDate, '-04:00') AS EDT;

Time Zone Swaps with TODATETIMEOFFSET

DECLARE @SomeDate DATETIME2(3) = '2016-09-04 02:28:29.681';

SELECT
    TODATETIMEOFFSET(
        DATEADD(HOUR, 7, @SomeDate), 
        '_02:00') AS BonnTime;

Discovering Time Zones

SELECT
    tzi.name, 
    tzi.current_utc_offset,
    tzi.is_currently_dst
FROM sys.time_zone_info tzi
WHERE 
    tzi.name LIKE '%Time Zone%';

Handling Invalid Dates

Error-safe date conversion functions

• Unsafe Functions
• Safe Functions