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

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:

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

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

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

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

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

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

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

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

18 thoughts on “How to get Day, Month and Year Part from DateTime in Sql Server

      1. Dear Sir/Madam,
        I have a small case that i am currently stuck with auto calculation to generate “Month” for Leave Travel Concession in miccro soft excel.

        Lets say that irrespective of year, those employees who joined service on 2nd July to 1st Jan- Their LTC to be paid on Jan of every year and those employees who joined on 2nd Jan to 1st July- Their LTC to be paid on July of every year.

        I am not able to generate the above case. Please do help me to resolve the issue.

  1. It woluld be nice to have a standard wayt o do it, a sql function that works in Sql Server, Postgresql, mysql and Oracle. Happens that this works on sql server and not in other database motors, and what works in all other database motors, don’t work in sql server.

Leave a Reply

Your email address will not be published. Required fields are marked *