Advanced Search

Return to Snippet

Revision: 30865
at January 5, 2011 01:41 by indra
Updated Code 
--SQL SERVER � Query to Find First and Last Day of Current Month
--May 13, 2007 by pinaldave 

--Following query will run respective to today�s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,'Last Day of Previous Month'
UNION

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

SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,'Last Day of Current Month'
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) , 'First Day of Next Month'

--GO
--Reference : Pinal Dave (http://blog.SQLAuthority.com)

Revision: 30864
at August 24, 2010 13:37 by indra
Initial Code 
--SQL SERVER – Query to Find First and Last Day of Current Month
--May 13, 2007 by pinaldave 

--Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,'Last Day of Previous Month'
UNION

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

SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,'Last Day of Current Month'
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) , 'First Day of Next Month'

--GO
--Reference : Pinal Dave (http://blog.SQLAuthority.com)
Initial URL 









Initial Description














Initial Title





Find First and Last Day of Current Month









Initial Tags














Initial Language





SQL