Get Day, Month & Year Parts Separately from Date in Microsoft SQL Server

Posted on by

Sometime we need to get Day, Month or Year part only from the given date.

We can get it using the DATEPART function.

It has following syntax:

DATEPART (datepart, date)

Where datepart is the part of date needs to be fetched like Day, Month or Year etc. and date is the actual date from where we need to extract this information.


Here are some examples to cover different use cases.

Get day, month & year parts from date

SELECT DATEPART(DAY, OrderDate) OrderDay, 
       DATEPART(MONTH, GETDATE()) OrderMonth, 
       DATEPART(YEAR, OrderDate) OrderYear,
       DATEPART(DAY, GETDATE()) Today
FROM Orders

Using DATEPART function in WHERE clause

Let’s suppose we want to fetch orders placed on particular day e.g. on 9th day of the month, we can do this using the query below:

SELECT * FROM Orders WHERE DATEPART(DAY, OrderDate) = 9

Following query fetches all orders made in the month April 1997

SELECT * FROM Orders WHERE DATEPART(MONTH, OrderDate) = 4 AND DATEPART(YEAR, OrderDate) = 1997

This function can also be used to fetch various types of information from a given date like Hour, Minutes, Seconds, Milliseconds, Microseconds, Nanoseconds, Week of the Year, Day of the Week, Day of the Year and Quarter of the Year etc.

Get hour part from date

SELECT DATEPART(HOUR, GETDATE()) Hour

Get minute part from date

SELECT DATEPART(MINUTE, GETDATE()) Minute

Get second part from date

SELECT DATEPART(SECOND, GETDATE()) Second

Get millisecond part from date

SELECT DATEPART(MILLISECOND, GETDATE()) Millisecond

Get microsecond part from date

SELECT DATEPART(MICROSECOND, GETDATE()) Microsecond

Get nanosecond part from date

SELECT DATEPART(NANOSECOND, GETDATE()) Nanosecond

Get day number of the day in year

SELECT DATEPART(DAYOFYEAR, GETDATE()) DayOfYear

Get day number of the day in week

SELECT DATEPART(WEEKDAY, GETDATE()) WeekDay

Get week number of the week in year

SELECT DATEPART(WEEK, GETDATE()) Week

Get quarter number of the quarter in year

SELECT DATEPART(QUARTER, GETDATE()) Quarter

DATAPART is very useful function that can be used to fetch or filter data according for particular day, month, year or quarter.