SQL SERVER – How To Find the First Day and Last Day of a Month

This is a quite common Interview question - “How To find the First and Last Days Of  a Month”. There are Multiple methods to achieve this.

A.  To find out the First Day of the current Month: 

In Order to find out the first day of the current month, we will use the following query :

Select Convert(varchar,DateAdd(d,-day(GetDate())+1, GetDate( ) ) ,101) 'First Day'

As discussed in the Earlier Articles, GetDate() returns the current date and time

Select GetDate() 'Current Date'

OutPut:

From this Current Date, I have extracted the Day, using the function DAY().

Select Day(GetDate())  'Day'

OutPut:

Now, In order to find the first day of the month, we have to subtract (the Current day –1) days,  i.e. (24-1) days,  from the current date. For this, DateADD() has been used.

As discussed Earlier, The Dateadd() function contains three arguments - Datepart,Integer and Date. Here,

Datepart is  ‘d’ which represents days                         Integer  is  -day(GetDate())+1 i.e.-24+1                      Date          is  The Current Date

On clubbing all the above functions, the final query is..

Select DateAdd(d,-day(GetDate())+1,GetDate()) 'First Day'

Output:

In order to convert the output into mm/dd/yyyy format,we can use the Convert() function.

Select Convert(Varchar,DateAdd(d,-day(GetDate())+1, GetDate()), 101) 'First Day'

Output:

B.  To find out the Last Day of the current Month: 

The Query to find out the Last day of the month is :

Select DateAdd(d,-Day(GetDate()),DateAdd(m,1,GetDate())) 'Last Day'

Here, moved a month ahead from the Current date and then subtracted the Current day,  to get the last day of the Current month.

Select DateAdd(m,1,GetDate()) 'Next Month'

Output:

From this, Current day of the month has been subtracted, using DateAdd() function.

Datepart is  ‘d’ which represents days                         Integer  is  -day(GetDate()) i.e.-24                                  Date    is  DateAdd(m,1,GetDate()), Next Month’s Date

The final Query is:

Select DateAdd(d,-Day(GetDate()),DateAdd(m,1,GetDate())) 'Last Day'

Output:

Again, on converting the output into mm/dd/yyyy format,

Select Convert(Varchar,DateAdd(d,-Day(GetDate()),DateAdd(m,1,GetDate())), 103) 'Last Day'

Output: