How to get Day, Month and Year Part from DateTime in Sql Server

In this article I am explain how to get Day, Month and Year Part from DateTime in Sql Server. 

1. DAY part of DateTime in Sql Server

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

Demo 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:

Demo 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(day,GETDATE()) 'Day Part'

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

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

Demo 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'

2. MONTH part of DateTime in Sql Server

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

Demo 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'

Demo 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'

Demo 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'

3. YEAR part of DateTime in Sql Server

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

Demo 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'

Demo 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'