Many a times we may need to get Day, Month and Year Part from DateTime in Sql Server. In this article we will see how we can get these parts of the DateTime in Sql Server.

You may like to read the other popular articles on Date and Time:

• How to get Date Part only from DateTime in Sql Server
• How to get Hour, Minute, Second, Millisecond and Time Part from DateTime in Sql Server
• Difference between DateTime and DateTime2 DataType

1. DAY part of DateTime in Sql Server

Following are the different ways of getting DAY part of the DateTime in Sql Server

[ALSO READ] How to get Day or Weekday name from date in Sql Server

Approach 1: Using DAY Function

We can use DAY() function to get the DAY part of the DateTime in Sql Server.

SELECT GETDATE() 'Today', DAY(GETDATE()) 'Day Part'

RESULT:

Approach 2: Using DATEPART Function

We can use DATEPART() function to get DAY part of the DateTime in Sql Server, here we need specify datepart parameter of the DATEPART function as day or dd or d all will return the same result.

SELECT GETDATE() 'Today', DATEPART(day,GETDATE()) 'Day Part'
SELECT GetDate() 'Today', DATEPART(dd,GETDATE())  'Day Part'
SELECT GetDate() 'Today', DATEPART(d,GETDATE())	  'Day Part'

RESULT:

Approach 3: Day returned should always be of TWO digits.

If we see the previous two approaches as Today is 3rd day of February, it is always returning day as 3 i.e one digit instead of 03. Below examples shows how to get two digits day part of a DateTime.

SELECT GETDATE() 'Today', 
  CONVERT(varchar(2), getdate(), 103) 'Day Part'
SELECT GETDATE() 'Today', 
  RIGHT('0' + CAST(DAY(GETDATE()) AS varchar(2)), 2) 'Day Part'

RESULT:

2. MONTH part of DateTime in Sql Server

Following are the different ways of getting MONTH part of the DateTime in Sql Server

[ALSO READ] How to get month name from date in Sql Server

Approach 1: Using MONTH Function

We can use MONTH() function to get the MONTH part of the DateTime in Sql Server.

SELECT GETDATE() 'Today', MONTH(GETDATE()) 'MONTH Part'

RESULT:

Approach 2: Using DATEPART Function

We can use DATEPART() function to get MONTH part of the DateTime in Sql Server, here we need specify datepart parameter of the DATEPART function as month or mm or m all will return the same result.

SELECT GETDATE() 'Today',DATEPART(month,GETDATE()) 'Month Part'
SELECT GetDate() 'Today', DATEPART(mm,GETDATE())  'Month Part'
SELECT GetDate() 'Today', DATEPART(m,GETDATE())	  'Month Part'

RESULT:

Approach 3: Month returned should always be of TWO digits.

If we see the previous two approaches as Today’s month is February, it is always returning month as 2 i.e one digit instead of 02. Below examples shows how to get two digits month part of a DateTime.

SELECT GETDATE() 'Today', 
 CONVERT(varchar(2), getdate(), 101) 'Month Part'
SELECT GETDATE() 'Today', 
 RIGHT('0'+CAST(MONTH(GETDATE()) AS varchar(2)),2) 'Month Part'

RESULT:

3. YEAR part of DateTime in Sql Server

Following are the different ways of getting YEAR part of the DateTime in Sql Server

Approach 1: Using YEAR Function

We can use YEAR() function to get the YEAR part of the DateTime in Sql Server.

SELECT GETDATE() 'Today', YEAR(GETDATE()) 'YEAR Part'

RESULT:

Approach 2: Using DATEPART Function

We can use DATEPART() function to get YEAR part of the DateTime in Sql Server, here we need specify datepart parameter of the DATEPART function as year or yyyy or yy all will return the same result.

SELECT GETDATE() 'Today', DATEPART(year,GETDATE()) 'Year Part'
SELECT GetDate() 'Today', DATEPART(yyyy,GETDATE()) 'Year Part'
SELECT GetDate() 'Today', DATEPART(yy,GETDATE())   'Year Part'

RESULT: