Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Date and Time Functions

Table of Contents

  1. GETDATE | Date Values
  2. Date Part Extractions (DATETRUNC, DATENAME, DATEPART, YEAR, MONTH, DAY)
  3. DATETRUNC – Data Aggregation
  4. EOMONTH
  5. Date Parts | Use Cases
  6. FORMAT
  7. CONVERT
  8. CAST
  9. DATEADD / DATEDIFF
  10. ISDATE

1. GETDATE | Date Values

Task 1 – Display order dates and current system date

Display OrderID, CreationTime, a hard-coded date '2025-08-20' as HardCoded, and the current system date as Today using GETDATE() from Sales.Orders.

💡 Suggested Answers 
SELECT
    OrderID,
    CreationTime,
    '2025-08-20' AS HardCoded,
    GETDATE() AS Today
FROM Sales.Orders;

2. Date Part Extractions (DATETRUNC, DATENAME, DATEPART, YEAR, MONTH, DAY)

Task 2 – Extract multiple date parts from CreationTime

For each order, extract different parts of CreationTime using:

  • DATETRUNC for year, day, and minute
  • DATENAME for month, weekday, day, year
  • DATEPART for year, month, day, hour, quarter, week
  • YEAR, MONTH, DAY

Return all of these in a single query.

💡 Suggested Answers 
SELECT
    OrderID,
    CreationTime,
    -- DATETRUNC Examples
    DATETRUNC(year, CreationTime) AS Year_dt,
    DATETRUNC(day, CreationTime) AS Day_dt,
    DATETRUNC(minute, CreationTime) AS Minute_dt,
    -- DATENAME Examples
    DATENAME(month, CreationTime) AS Month_dn,
    DATENAME(weekday, CreationTime) AS Weekday_dn,
    DATENAME(day, CreationTime) AS Day_dn,
    DATENAME(year, CreationTime) AS Year_dn,
    -- DATEPART Examples
    DATEPART(year, CreationTime) AS Year_dp,
    DATEPART(month, CreationTime) AS Month_dp,
    DATEPART(day, CreationTime) AS Day_dp,
    DATEPART(hour, CreationTime) AS Hour_dp,
    DATEPART(quarter, CreationTime) AS Quarter_dp,
    DATEPART(week, CreationTime) AS Week_dp,
    YEAR(CreationTime) AS Year,
    MONTH(CreationTime) AS Month,
    DAY(CreationTime) AS Day
FROM Sales.Orders;

3. DATETRUNC – Data Aggregation

Task 3 – Aggregate orders by year with DATETRUNC

Using DATETRUNC, aggregate orders from Sales.Orders by year (based on CreationTime) and return the truncated year and the count of orders per year.

💡 Suggested Answers 
SELECT
    DATETRUNC(year, CreationTime) AS Creation,
    COUNT(*) AS OrderCount
FROM Sales.Orders
GROUP BY DATETRUNC(year, CreationTime);

4. EOMONTH

Task 4 – Show end-of-month for each CreationTime

For each order, show OrderID, CreationTime, and the end of that month using EOMONTH(CreationTime).

💡 Suggested Answers 
SELECT
    OrderID,
    CreationTime,
    EOMONTH(CreationTime) AS EndOfMonth
FROM Sales.Orders;

5. Date Parts | Use Cases

Task 5 – Orders per year

Count how many orders were placed each year using YEAR(OrderDate) and GROUP BY.

💡 Suggested Answers 
SELECT 
    YEAR(OrderDate) AS OrderYear, 
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY YEAR(OrderDate);

Task 6 – Orders per month (numeric month)

Count how many orders were placed each month using MONTH(OrderDate).

💡 Suggested Answers 
SELECT 
    MONTH(OrderDate) AS OrderMonth, 
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY MONTH(OrderDate);

Task 7 – Orders per month (friendly month names)

Count how many orders were placed each month, using DATENAME(month, OrderDate) to show the month name.

💡 Suggested Answers 
SELECT 
    DATENAME(month, OrderDate) AS OrderMonth, 
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY DATENAME(month, OrderDate);

Task 8 – Orders in February

Show all orders that were placed during the month of February, using MONTH(OrderDate).

💡 Suggested Answers 
SELECT
    *
FROM Sales.Orders
WHERE MONTH(OrderDate) = 2;

6. FORMAT

Task 9 – Format CreationTime with various patterns

For each order, show OrderID, CreationTime, and formatted versions using FORMAT:

  • 'MM-dd-yyyy' as USA_Format
  • 'dd-MM-yyyy' as EURO_Format
  • 'dd', 'ddd', 'dddd', 'MM', 'MMM', 'MMMM' in separate columns
💡 Suggested Answers 
SELECT
    OrderID,
    CreationTime,
    FORMAT(CreationTime, 'MM-dd-yyyy') AS USA_Format,
    FORMAT(CreationTime, 'dd-MM-yyyy') AS EURO_Format,
    FORMAT(CreationTime, 'dd') AS dd,
    FORMAT(CreationTime, 'ddd') AS ddd,
    FORMAT(CreationTime, 'dddd') AS dddd,
    FORMAT(CreationTime, 'MM') AS MM,
    FORMAT(CreationTime, 'MMM') AS MMM,
    FORMAT(CreationTime, 'MMMM') AS MMMM
FROM Sales.Orders;

Task 10 – Custom formatted CreationTime

Display CreationTime in a custom string format like: Day Wed Jan Q1 2025 12:34:56 PM by combining FORMAT and DATENAME(quarter, ...).

💡 Suggested Answers 
SELECT
    OrderID,
    CreationTime,
    'Day ' + FORMAT(CreationTime, 'ddd MMM') +
    ' Q' + DATENAME(quarter, CreationTime) + ' ' +
    FORMAT(CreationTime, 'yyyy hh:mm:ss tt') AS CustomFormat
FROM Sales.Orders;

Task 11 – Orders per month-year (e.g., “Jan 25”)

Using FORMAT(CreationTime, 'MMM yy'), count how many orders were placed for each "MMM yy" period.

💡 Suggested Answers 
SELECT
    FORMAT(CreationTime, 'MMM yy') AS OrderDate,
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY FORMAT(CreationTime, 'MMM yy');

7. CONVERT

Task 12 – Demonstrate CONVERT for dates and strings

Use CONVERT to:

  • Convert '123' to INT
  • Convert '2025-08-20' to DATE
  • Convert CreationTime to DATE
  • Convert CreationTime to VARCHAR with two different styles (32 and 34)
💡 Suggested Answers 
SELECT
    CONVERT(INT, '123') AS [String to Int CONVERT],
    CONVERT(DATE, '2025-08-20') AS [String to Date CONVERT],
    CreationTime,
    CONVERT(DATE, CreationTime) AS [Datetime to Date CONVERT],
    CONVERT(VARCHAR, CreationTime, 32) AS [USA Std. Style:32],
    CONVERT(VARCHAR, CreationTime, 34) AS [EURO Std. Style:34]
FROM Sales.Orders;

8. CAST

Task 13 – Demonstrate CAST for type conversion

Using CAST, convert:

  • '123' to INT
  • 123 to VARCHAR
  • '2025-08-20' to DATE and DATETIME2
  • CreationTime to DATE
💡 Suggested Answers 
SELECT
    CAST('123' AS INT) AS [String to Int],
    CAST(123 AS VARCHAR) AS [Int to String],
    CAST('2025-08-20' AS DATE) AS [String to Date],
    CAST('2025-08-20' AS DATETIME2) AS [String to Datetime],
    CreationTime,
    CAST(CreationTime AS DATE) AS [Datetime to Date]
FROM Sales.Orders;

9. DATEADD / DATEDIFF

Task 14 – Date arithmetic on OrderDate

For each order, show:

  • OrderDate
  • TenDaysBefore = 10 days before OrderDate
  • ThreeMonthsLater = 3 months after OrderDate
  • TwoYearsLater = 2 years after OrderDate

using DATEADD.

💡 Suggested Answers 
SELECT
    OrderID,
    OrderDate,
    DATEADD(day, -10, OrderDate) AS TenDaysBefore,
    DATEADD(month, 3, OrderDate) AS ThreeMonthsLater,
    DATEADD(year, 2, OrderDate) AS TwoYearsLater
FROM Sales.Orders;

Task 15 – Calculate employee age

Using DATEDIFF, calculate the age (in years) of employees based on BirthDate and current date from Sales.Employees.

💡 Suggested Answers 
SELECT
    EmployeeID,
    BirthDate,
    DATEDIFF(year, BirthDate, GETDATE()) AS Age
FROM Sales.Employees;

Task 16 – Average shipping duration per month

From Sales.Orders, compute the average shipping duration in days for each month (based on OrderDate and ShipDate).

💡 Suggested Answers 
SELECT
    MONTH(OrderDate) AS OrderMonth,
    AVG(DATEDIFF(day, OrderDate, ShipDate)) AS AvgShip
FROM Sales.Orders
GROUP BY MONTH(OrderDate);

Task 17 – Time gap between consecutive orders

Using DATEDIFF and LAG, compute for each order:

  • CurrentOrderDate
  • PreviousOrderDate
  • NrOfDays = number of days between the previous order and the current one
💡 Suggested Answers 
SELECT
    OrderID,
    OrderDate AS CurrentOrderDate,
    LAG(OrderDate) OVER (ORDER BY OrderDate) AS PreviousOrderDate,
    DATEDIFF(day, LAG(OrderDate) OVER (ORDER BY OrderDate), OrderDate) AS NrOfDays
FROM Sales.Orders;

10. ISDATE

Task 18 – Validate dates and convert valid ones

Given a derived table with several OrderDate strings, use ISDATE to:

  • Show whether each string is a valid date (IsValidDate)
  • Convert valid values to DATE and otherwise set them to '9999-01-01' in NewOrderDate

(Optionally, you can filter only invalid rows using the commented WHERE.)

💡 Suggested Answers 
SELECT
    OrderDate,
    ISDATE(OrderDate) AS IsValidDate,
    CASE 
        WHEN ISDATE(OrderDate) = 1 THEN CAST(OrderDate AS DATE)
        ELSE '9999-01-01'
    END AS NewOrderDate
FROM (
    SELECT '2025-08-20' AS OrderDate UNION
    SELECT '2025-08-21' UNION
    SELECT '2025-08-23' UNION
    SELECT '2025-08'
) AS t
-- WHERE ISDATE(OrderDate) = 0