SQL DATEPART Function

DATEPART returns an integer datepart value for a given date.

Dateparts include year, month, day, minute, and others (values listed below).

Example

#

This example returns the month for the specified date.

SELECT DATEPART(month, '2022-03-19') as Month
Result:  1 record
Month
3

Using DATEPART

#

A common use is to get a datepart from the current date.
In this example the datepart is day of week.

SELECT DATEPART(weekday, GETDATE()) as WeekDay
Result:  1 record
Weekday
0

Syntax

Syntax of the DATEPART function .

DATEPART(date_part, input_date) 

date_part -- the part of date requested (datepart values are listed below).

input_date -- a date from which to extract the datepart.

These are valid date_part values and possible abbreviations

Date part Abbreviation
year yyyy or yy
quarter qq or q
month mm or m
dayofyear dy or y
day dd or d
week wk or ww
weekday dw
hour hh
minute mi or n
second ss or s
millisecond ms
microsecond mcs
nanosecond ns

More Examples

DATEPART. with YEAR

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List order counts by year -- most recent years first.
SELECT DATEPART(year, OrderDate) AS Year,
       COUNT(TotalAmount) AS 'Total Orders' 
  FROM [Order]
 GROUP BY DATEPART(year, OrderDate)
 ORDER BY DATEPART(year, OrderDate) DESC
Result:  3 records
Year Total Orders
2014 270
2013 408
2012 152

DATEPART with GROUP BY and ORDER BY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List total sales by month for the year 2013.
SELECT DATEPART(month, OrderDate) AS Month,
       SUM(TotalAmount) AS 'Total Sales'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY DATEPART(month, OrderDate)
 ORDER BY DATEPART(month, OrderDate)
Result:  12 records
Month Total Sales
1 66692.80
2 41207.20
3 39979.90
4 55699.39
5 56823.70

You may also like



Guides


vsn 3.1