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
