# SQL DATEPART Function: Extract Date Parts with Examples

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:

```sql
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`.

```sql
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:

```sql
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:

```sql
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`:

```sql
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](/blog/data-analysis/sql-dateadd-datediff). If you need to turn a string into a date first, [SQL Convert Date: Functions, Formats and Examples](/blog/data-analysis/sql-convert-date) 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](/blog/data-analysis/sql-where-date-is-today). For the function that returns the current date itself, see [SQL CURRENT_DATE: Syntax, Examples and Today's Date Queries](/blog/data-analysis/sql-current-date-function).

## 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.

## References

1. [Advanced Edit (Condition) Dialog Box - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/policy-based-management/advanced-edit-condition-dialog-box?view=sql-server-ver17)
2. [Write International Transact-SQL Statements - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/collations/write-international-transact-sql-statements?view=sql-server-ver17)
3. [SqlFunctions.DateName Method (System.Data.Objects.SqlClient) | Microsoft Learn](https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.sqlclient.sqlfunctions.datename?view=netframework-4.8.1)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [SQL Convert Date: Functions, Formats and Examples](/blog/data-analysis/sql-convert-date)
- [SQL DATEADD and DATEDIFF: Syntax and Examples](/blog/data-analysis/sql-dateadd-datediff)
- [SQL WHERE Date Is Today: Syntax and Examples](/blog/data-analysis/sql-where-date-is-today)
- [SQL CURRENT_DATE: Syntax, Examples and Today's Date Queries](/blog/data-analysis/sql-current-date-function)
- [DATEDIF Excel Function: Syntax and Examples](/blog/data-analysis/datedif-excel-function)