SQL DATEPART Function

Saturday, December 17, 2011

tags SQL

The DATEPART function is used in Microsoft SQL Server to return a single part of a date/time, such as year, month, day, etc..

Employees Table

employeeIDemployeeNamehireDate
1000John Smith1995-12-03
1001Fred White2001-10-12
1002Jane Scott1998-05-01
1003Samuel Williams1991-01-03


In this example, we want to find out what is the employees' year of hire.


Syntax

DATEPART(datepart, column_name)


DATEPART Reference Table

datepartabbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
dayd
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKisowk, isoww


Example
SELECT employeeName as [Employee Name], DATEPART(year,hireDate) as [Year Hired]
FROM employees


Results

Employee NameYear Hired
John Smith1995
Fred White2001
Jane Scott1998
Samuel Williams1991

The SQL DATEPART function can be very useful when you are interested in formatting the results using a speficic format of a date and/or time.

Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook