Date & Time Basics: YEAR, MONTH, DAY, DATEPART, DATENAME

"How many players debuted in 2008?", "Which month had the most runs?", "On which weekday was the match played?" — all of these need a part of a date. SQL Server's date & time functions answer them.

Definition — the three kinds of date/time values

A date/time data type stores a point in time in a special format that SQL understands, so it can extract parts, compare and calculate — something it cannot do with plain text.

DATETIME2 is SQL Server's name for what other databases call a TIMESTAMP. (SQL Server also has the older DATETIME; prefer DATETIME2 for new tables — it is more precise.) Always write date literals as 'YYYY-MM-DD' — this ISO format is understood the same way on every server setting.

GETDATE() — the current date and time

GETDATE() returns the current date and time of the database server as a DATETIME value. It takes no arguments and gives a different answer every time you run it — useful for "today", ages and "days since" calculations.

SELECT GETDATE() AS right_now;

The result is today's date and time, e.g. 2025-08-20 18:55:45.840 (yours will differ).

The four families of date & time functions

Part-extraction overview

Syntax

YEAR(date)    MONTH(date)    DAY(date)
DATEPART(part, date)     -- part: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second
DATENAME(part, date)

The part is written without quotes (DATEPART(month, debut)). Short forms also work: yy, qq, mm, dd, dw, hh, mi, ss.

YEAR, MONTH, DAY — the quick functions

YEAR, MONTH and DAY each extract one part of a date and return it as an INT. They are shortcuts for DATEPART(year, …), DATEPART(month, …) and DATEPART(day, …).

SELECT
    YEAR('2025-08-20')  AS y,
    MONTH('2025-08-20') AS m,
    DAY('2025-08-20')   AS d;

DATEPART — any part as a number

DATEPART(part, date) returns the requested part of a date as an integer. It can return parts that have no shortcut function, such as quarter, week, weekday and hour.

SELECT
    DATEPART(year, '2025-08-20')    AS yr,
    DATEPART(month, '2025-08-20')   AS mon,
    DATEPART(quarter, '2025-08-20') AS qtr,
    DATEPART(weekday, '2025-08-20') AS wkday;

Weekday: with SQL Server's default (US English) settings Sunday = 1, so Wednesday = 4. The first day of the week depends on the SET DATEFIRST setting, so don't hard-code weekday numbers across servers — use DATENAME when you need the day.

DATENAME — a part as text

DATENAME(part, date) returns the requested part as a string (text). For month and weekday it returns the name ('August', 'Wednesday'); for numeric parts like year it returns the number as text ('2025').

SELECT
    DATENAME(month, '2025-08-20')   AS month_name,
    DATENAME(weekday, '2025-08-20') AS day_name,
    DATENAME(year, '2025-08-20')    AS year_text;

Perfect for readable reports. But because the result is text, sorting by it gives alphabetical order (April, August, December …) — sort by MONTH(date) instead.

Example — date functions in a query

SELECT
    name,
    debut,
    YEAR(debut)                        AS debut_year,
    DATENAME(month, debut)             AS month,
    DATEDIFF(year, debut, '2025-08-20') AS years
FROM player_profiles;

How it works: YEAR gives an INT, DATENAME gives text, and DATEDIFF counts how many year boundaries were crossed between the debut and 2025-08-20 (covered in Date Calculations). The debut column lives in player_profiles, our version of the PDF's chapter-7 players table.

Common mistakes

• ❌ DATEPART('month', debut) with quotes → error in SQL Server. ✅ DATEPART(month, debut).
• ❌ ORDER BY DATENAME(month, d) expecting calendar order. ✅ ORDER BY MONTH(d).
• ❌ WHERE YEAR(debut) = 2008 on a huge table — it works, but wrapping the column in a function stops SQL Server from using an index. ✅ Prefer a range: WHERE debut >= '2008-01-01' AND debut < '2009-01-01'.
• ❌ Writing dates as '20/08/2025' — it may be misread depending on language settings. ✅ Use '2025-08-20'.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: What is the difference between DATEPART and DATENAME? Both extract a part of a date. DATEPART returns an INT (DATEPART(month, '2025-08-20') = 8), DATENAME returns a string ('August'). Use DATEPART for calculations and sorting, DATENAME for display.