Query to Find First and Last Day

Below  queries will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Current Month and First Day of Next Month respective to current month.

SQL statements:

DECLARE @Date DATETIME
SELECT @Date = GETDATE()

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)),@Date),101) As Date,
‘Last Day of Previous Month’ As DateDetail
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)-1),@Date),101) AS Date_Value,
‘First Day of Current Month’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@Date,101) AS Date_Value, ‘Today’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),101) ,
‘Last Day of Current Month’
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))-1),DATEADD(mm,1,@Date)),101) ,
‘First Day of Next Month’
GO

Output:

Date                                 DateDetail
08/31/2014                   Last Day of Previous Month
09/01/2014                   First Day of Current Month
09/26/2014                   Today
09/30/2014                   Last Day of Current Month
10/01/2014                   First Day of Next Month

Advertisements