Today someone asked how to get the first day and last day of the month in T-Sql query. So I write the query for both case one just get the first and last day of current month as well as the first and last day of the passing date value.

1. First query gives you the first day and last day of current month.

DECLARE @inputdate DATETIME

SELECT @inputdate = GETDATE()

SELECT 'First Day of Current Month' AS Date_Type, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@inputdate)-1),@inputdate),101) AS Date

UNION

SELECT 'Today' AS Date_Type,CONVERT(VARCHAR(25),@inputdate,101) AS Date

UNION

SELECT 'Last Day of Current Month' AS Date_Type, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@inputdate))),DATEADD(mm,1,@inputdate)),101) AS Date

 

Output is

Date_Type                             Date

First Day of Current Month       03/01/2013

Last Day of Current Month       03/31/2013

Today  03/04/2013

 

2.2nd query for the getting first and last day of the month when passing a date value

Eg. Let passing value is 20130217 for date 17 fab 2013.

 

DECLARE @inputdate DATETIME

SELECT @inputdate = Convert(datetime,'20130217')

Print @inputdate

SELECT 'First Day of Month for passing date' AS Date_Type, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@inputdate)-1),@inputdate),101) AS Date

UNION

SELECT 'Last Day of Month for passing date',CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@inputdate))),DATEADD(mm,1,@inputdate)),101)

 

Output is

Date_Type                                         Date

First Day of Month for passing date      02/01/2013

Last Day of Month for passing date       02/28/2013