SQL DATEPART Function: Extract Date Parts with Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

Extracting a year, month or weekday from a date column is one of the most common tasks in SQL reporting. The DATEPART function does exactly that: it takes a date and a date part name and returns that part as an integer. This article covers the syntax, the supported date parts, worked examples and the equivalents in other database engines.

Quick Answer

DATEPART(datepart, date) returns an integer for the requested part of a date, so DATEPART(month, '2024-08-06') returns 8 [1].
Supported parts include year (yy, yyyy), month (mm, m) and dayofyear (dy, y), plus day, week, weekday, hour, minute and second [1].
Use the numeric date parts in comparisons and logic, because month and weekday names change with the language setting [2].
DATEPART is a SQL Server and T-SQL function. MySQL uses YEAR(), MONTH() and DAY(), PostgreSQL uses EXTRACT(), and SQLite uses strftime().
The result is an integer, so you can group by it, filter on it or use it in arithmetic without converting a string.

Syntax

SQL Server syntax:

DATEPART(datepart, date)

Argument	Required?	Meaning
`datepart`	Yes	The part of the date to return, given as a keyword or abbreviation such as `year`, `yy`, `month`, `mm`, `day`, `dd`, `weekday`, `dw` [1]
`date`	Yes	An expression that returns a datetime value, or a character string in a date format [1]

The function returns an integer that represents the specified date part of the specified date [1]. A related function, DATENAME, returns the same part as a character string instead, which is why DATENAME(MONTH, GETDATE()) gives you a month name and DATEPART(MONTH, GETDATE()) gives you a number [2][3].

How It Works

DATEPART reads the date value you pass in, identifies the calendar component you named, and returns it as a whole number. The date argument can be a column, a literal string, or the result of another function such as GETDATE().

The date part keyword is not case sensitive, and most parts have short abbreviations. year, yy and yyyy all mean the same thing, as do month, mm and m, and dayofyear, dy and y [1]. Using the full keyword makes queries easier to read, so DATEPART(month, order_date) is clearer than DATEPART(m, order_date).

The return type is an integer, which matters for how you use the result. You can compare it directly to a number, group rows by it, or add it to another integer. If you need the name of a month or weekday for display, use DATENAME instead, but keep the numeric form for any logic that must behave the same way in every language [2].

Because the output is a plain number, DATEPART is often the first step in a date-based aggregation. Extracting the month lets you build monthly totals, and extracting the weekday lets you compare activity across days of the week.

Worked Example

The sales table below holds six sales, each with a date and an amount. The goal is to pull the year and month out of each sale_date so the rows can be grouped later.

Input table sales:

sale_id	sale_date	amount
1	2024-01-15	120.50
2	2024-02-20	89.99
3	2024-03-05	250.00
4	2024-04-12	175.25
5	2024-05-30	310.75
6	2024-06-18	95.00

The query below uses SQLite's strftime, which is the equivalent of DATEPART(year, ...) and DATEPART(month, ...) in engines that do not have DATEPART.

SELECT
  sale_id,
  sale_date,
  strftime('%Y', sale_date) AS sale_year,
  strftime('%m', sale_date) AS sale_month
FROM sales
ORDER BY sale_id;

Result:

sale_id	sale_date	sale_year	sale_month
1	2024-01-15	2024	01
2	2024-02-20	2024	02
3	2024-03-05	2024	03
4	2024-04-12	2024	04
5	2024-05-30	2024	05
6	2024-06-18	2024	06

The result was checked with an equivalent SQLite query. strftime('%Y', sale_date) returns the four-digit year, mirroring DATEPART(year, sale_date), and strftime('%m', sale_date) returns the two-digit month, mirroring DATEPART(month, sale_date). In SQL Server the same logic would be written as DATEPART(year, sale_date) and DATEPART(month, sale_date), and both would return integers rather than the zero-padded strings shown here.

More Examples

Filter by a single month. To keep only rows from March, compare the extracted month to a number:

SELECT sale_id, sale_date, amount
FROM sales
WHERE DATEPART(month, sale_date) = 3;

Group sales by month. Extracting the month gives you a grouping key for monthly totals:

SELECT
  DATEPART(year, sale_date) AS sale_year,
  DATEPART(month, sale_date) AS sale_month,
  SUM(amount) AS total_amount
FROM sales
GROUP BY DATEPART(year, sale_date), DATEPART(month, sale_date)
ORDER BY sale_year, sale_month;

Find the weekday. DATEPART(weekday, sale_date) returns a number for the day of the week. The number depends on the session's DATEFIRST setting, so do not assume Monday is always 1.

Extract several parts at once. You can call the function more than once in the same SELECT:

SELECT
  sale_date,
  DATEPART(year, sale_date) AS sale_year,
  DATEPART(month, sale_date) AS sale_month,
  DATEPART(day, sale_date) AS sale_day
FROM sales;

Use it with a computed date. The date argument accepts any expression that returns a datetime value, so you can combine it with functions that shift dates. If you need to move a date before extracting a part, see SQL DATEADD and DATEDIFF: Syntax and Examples. If you need to turn a string into a date first, SQL Convert Date: Functions, Formats and Examples covers the conversion styles.

Build a today-based filter. Combining DATEPART with the current date lets you isolate today's rows, which is covered in SQL WHERE Date Is Today: Syntax and Examples. For the function that returns the current date itself, see SQL CURRENT_DATE: Syntax, Examples and Today's Date Queries.

Errors and How to Fix Them

"no such function: DATEPART" or "function datepart(...) does not exist". You are running the query in an engine that does not implement DATEPART, such as MySQL, PostgreSQL or SQLite. Use the engine's own function instead. MySQL has YEAR(), MONTH() and DAY(). PostgreSQL has EXTRACT(YEAR FROM date_column). SQLite has strftime('%Y', date_column).

"Argument data type ... is invalid for argument 1 of datepart function." The first argument must be a date part keyword, not a column or a number. Swap the arguments so the keyword comes first and the date expression comes second.

"Conversion failed when converting date and/or time from character string." The date argument is a string that the engine cannot parse under the current language and format settings. Convert it explicitly with a style parameter so the interpretation does not depend on connection settings [2].

Unexpected weekday numbers. The weekday value depends on the DATEFIRST setting for the session. If your numbers do not match what you expect, check that setting before you rely on a specific number.

Common Mistakes

Using month or weekday names in logic. Names change with the language setting, so DATENAME(MONTH, GETDATE()) returns May in U.S. English, Mai in German and mai in French [2]. Fix: use DATEPART and compare numbers instead.
Assuming weekday 1 is always Monday. The first day of the week is controlled by DATEFIRST, so the same date can return different weekday numbers in different sessions. Fix: set DATEFIRST explicitly or map the numbers yourself.
Forgetting that the result is an integer. DATEPART(month, ...) returns 8, not '08'. Fix: format the number for display with FORMAT or string padding if you need a leading zero.
Filtering on a function and losing index use. WHERE DATEPART(year, order_date) = 2024 cannot use an index on order_date in the usual way. Fix: filter with a date range such as order_date >= '2024-01-01' AND order_date < '2025-01-01'.
Mixing up DATEPART and DATENAME. One returns a number and the other returns a string [3]. Fix: pick DATEPART for calculations and DATENAME only for labels shown to users.
Passing the arguments in the wrong order. The date part comes first and the date second. Fix: read the call as "which part, of which date."

Limitations

DATEPART is a T-SQL function, so it does not run unchanged in MySQL, PostgreSQL, Oracle or SQLite. Each engine has its own way to pull a date part, and the return types differ. SQLite's strftime returns text such as '01' for the month, while DATEPART returns the integer 1. Porting a query means rewriting the extraction, not just renaming the function.

The function also strips context. Once you extract a month, you lose the year, so grouping by month alone merges January 2023 and January 2024 into one bucket. Extract every part you need for the grouping key. Weekday numbers depend on the session's DATEFIRST setting, which makes them unreliable across environments unless you control that setting. And wrapping a date column in DATEPART inside a WHERE clause usually prevents the query optimizer from using an index on that column, which can slow down large tables.

Frequently Asked Questions

What is the difference between DATEPART and DATENAME in SQL?

DATEPART returns the requested part as an integer, and DATENAME returns it as a character string [3]. So DATEPART(month, '2024-08-06') gives you 8, while DATENAME(month, '2024-08-06') gives you the month name. Use DATEPART for comparisons, grouping and arithmetic, and DATENAME when you need a label for a report.

How do I get the month from a date in MySQL?

MySQL does not have DATEPART. Use MONTH(date_column) to get the month number, YEAR(date_column) for the year and DAY(date_column) for the day. These return integers, so they behave like DATEPART in filters and GROUP BY clauses.

How do I extract the year and month in PostgreSQL?

PostgreSQL uses EXTRACT, written as EXTRACT(YEAR FROM date_column) or EXTRACT(MONTH FROM date_column). The result is a numeric value, so cast it to an integer if you need one. You can also use date_trunc('month', date_column) when you want a truncated date instead of a number.

Why does DATEPART(weekday, ...) return a different number than I expect?

The weekday number depends on the DATEFIRST setting for the session, which defines which day counts as the first day of the week. Two sessions with different DATEFIRST values can return different numbers for the same date. Set DATEFIRST explicitly at the start of your script, or convert the number to a name with DATENAME for display.

Can I use DATEPART in a WHERE clause?

Yes, but it usually prevents the database from using an index on the date column, because the column is wrapped in a function. For a single year or month, a date range filter such as order_date >= '2024-01-01' AND order_date < '2025-01-01' is faster on large tables. Keep DATEPART in the SELECT list and GROUP BY clause where it does not block index use.