Date Functions in SQL

SQL Study Notes – AdventureWorksDW2017

Common Date Functions

1. Get Year, Month, Day

SELECT 
    OrderDate,
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    DAY(OrderDate) AS OrderDay
FROM FactInternetSales;

2. Format Date as mm/dd/yyyy

SELECT 
    OrderDate,
    CONVERT(VARCHAR, OrderDate, 101) AS FormattedDate   -- mm/dd/yyyy
FROM FactInternetSales;

3. Filter by Year

SELECT *
FROM FactInternetSales
WHERE YEAR(OrderDate) = 2013;

Most Useful Date Format Codes

• 101 → mm/dd/yyyy
• 102 →yyyy.mm.dd
• 110 → mm-dd-yy
• 112 → yyyymmdd
• 120 → yyyy-mm-dd
• 121 → yyyy-mm-dd hh:mi:ss.mmm
• 100 → mon dd yyyy
• 23 →  yyyy-mm-dd

SELECT CONVERT(varchar, GETDATE(), 102); — 2026.05.22

Key Takeaway

Use YEAR(), MONTH(), DAY() for filtering and grouping.
Use CONVERT(..., 101) when you need to display dates in mm/dd/yyyy format.