<aside> 📅 Date functions extract, format, and calculate with date/time values in SQL Server.

</aside>

1. GETDATE() — Current Date and Time

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

2. Extract Date Parts

SELECT
    OrderID,
    YEAR(OrderDate)  AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    DAY(OrderDate)   AS OrderDay,
    DATEPART(quarter, OrderDate) AS Quarter,
    DATEPART(week,    OrderDate) AS WeekNumber,
    DATENAME(month,   OrderDate) AS MonthName,
    DATENAME(weekday, OrderDate) AS DayName
FROM Sales.Orders;

3. DATETRUNC() — Truncate to a Time Unit

SELECT
    DATETRUNC(year,   CreationTime) AS TruncYear,
    DATETRUNC(month,  CreationTime) AS TruncMonth,
    DATETRUNC(day,    CreationTime) AS TruncDay
FROM Sales.Orders;

-- Aggregate orders by year
SELECT
    DATETRUNC(year, CreationTime) AS OrderYear,
    COUNT(*) AS TotalOrders
FROM Sales.Orders
GROUP BY DATETRUNC(year, CreationTime);

4. EOMONTH() — End of Month

SELECT
    OrderDate,
    EOMONTH(OrderDate) AS EndOfMonth
FROM Sales.Orders;

5. FORMAT() — Custom Date Formatting

SELECT
    FORMAT(CreationTime, 'MM-dd-yyyy') AS USA_Format,
    FORMAT(CreationTime, 'dd-MM-yyyy') AS EURO_Format,
    FORMAT(CreationTime, 'dddd')       AS FullDayName,
    FORMAT(CreationTime, 'MMMM')       AS FullMonthName,
    FORMAT(CreationTime, 'MMM yy')     AS ShortMonthYear
FROM Sales.Orders;

6. CONVERT() & CAST() — Type Conversion

-- CONVERT
SELECT
    CONVERT(INT,  '123')               AS StringToInt,
    CONVERT(DATE, '2025-08-20')        AS StringToDate,
    CONVERT(DATE, CreationTime)        AS DateTimeToDate
FROM Sales.Orders;

-- CAST
SELECT
    CAST('123' AS INT)                 AS StringToInt,
    CAST(123   AS VARCHAR)             AS IntToString,
    CAST('2025-08-20' AS DATE)         AS StringToDate,
    CAST(CreationTime AS DATE)         AS DateTimeToDate
FROM Sales.Orders;