SQL Server DATEPART() Function

Summary: in this tutorial, you will learn how to use the SQL Server DATEPART() function to extract a part of a date.

SQL Server DATEPART() function overview

The DATEPART() function returns an integer that is a part of a date such as a day, month, or year.

Here’s the syntax of the DATEPART() function:

DATEPART ( date_part , input_date )Code language: SQL (Structured Query Language) (sql)

The DATEPART() takes two arguments:

date_part is the part of a date to be extracted. ( See the valid date parts in the table below).
input_date is the date from which the date part is extracted.

date_part	abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

SQL Server `DATEPART()` examples

Let’s take some examples of using the DATEPART() function.

1) Using DATEPART() function with variables

This example uses the DATEPART() function to extract various parts of a date value stored in a variable:

DECLARE @d DATETIME = '2019-01-01 14:30:14';
SELECT 
   DATEPART(year, @d) year, 
   DATEPART(quarter, @d) quarter, 
   DATEPART(month, @d) month, 
   DATEPART(day, @d) day, 
   DATEPART(hour, @d) hour, 
   DATEPART(minute, @d) minute, 
   DATEPART(second, @d) second;Code language: SQL (Structured Query Language) (sql)

Here is the output:

2) Using DATEPART() function with table columns example

We will use the sales.orders and sales.order_items from the sample database for demonstration.

The following example uses the DATEPART() function to query the gross sales by year, quarter, month, and day.

SELECT 
  DATEPART(year, shipped_date) [year], 
  DATEPART(quarter, shipped_date) [quarter], 
  DATEPART(month, shipped_date) [month], 
  DATEPART(day, shipped_date) [day], 
  SUM(quantity * list_price) gross_sales 
FROM 
  sales.orders o 
  INNER JOIN sales.order_items i ON i.order_id = o.order_id 
WHERE 
  shipped_date IS NOT NULL 
GROUP BY 
  DATEPART(year, shipped_date), 
  DATEPART(quarter, shipped_date), 
  DATEPART(month, shipped_date), 
  DATEPART(day, shipped_date) 
ORDER BY 
  [year] DESC, 
  [quarter], 
  [month], 
  [day];Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

SQL Server DATEPART Function with table column example

In this example, we used the DATEPART() function to extract year, quarter, month, and day from the values in the shipped_date column. In the GROUP BY clause, we aggregated the gross sales ( quantity * list_price) by these date parts.

Note that you can use the DATEPART() function in the SELECT, WHERE, HAVING, GROUP BY, and ORDER BY clauses.

Summary

Use the SQL Server DATEPART() to extract a date part from a date.

Was this tutorial helpful?