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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s