Building Dates

Building Dates

• Building a Date

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

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

• Breakding 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 statements returns YEAR = 2019, 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 databses have this function

• Using the CAST() function

        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 formattting from dates to strings using the style parameter
  • Specific to T-SQL

        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

    Style Code	Format
    1 / 101	United States m/d/y
    3 / 103	British/French d/m/y
    4 / 104	German d.m.y
    11 / 111	Japanese y/m/d
    12 / 112	ISO Standard yyyymmdd
    20 / 120	ODBC Standard (121 for ms)
    126	ISO8601 yyyy-mm-dd hh:mi:ss.mmm
    127	yyyy-mm-ddThh:mi:ss.mmmZ

• 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;