Sunday, June 17, 2007

SQL First and Last Day Of a Month

In This SQL Tutorial show you retrieve First Day and Last Day for a month using SQL Query.
It can dynamically to retrieve First Day and Last day for a month using SQL Query.


Example SQL Statement or Query below show you how to retrieve First Day or Last Day for a month.
To display the date in different format, click here.

DECLARE @Today DATETIME
SELECT @Today = '6/17/2007'


Get First Day of a Month Using SQL Query
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-3,@Today))

Value = 2007-03-01 00:00:00.000


SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-2,@Today))
Value = 2007-04-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today))
Value = 2007-05-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))
Value = 2007-06-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))
Value = 2007-07-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,2,@Today))
Value = 2007-08-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,3,@Today))
Value = 2007-09-01 00:00:00.000


Get Last Day of a Month Using SQL Query
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-2,@Today))
Value = 2007-03-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-1,@Today))
Value = 2007-04-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,0,@Today))
Value = 2007-05-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))
Value = 2007-06-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,2,@Today))
Value = 2007-07-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,3,@Today))
Value = 2007-08-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,4,@Today))
Value = 2007-09-30 00:00:00.000

21 comments:

Kalpana said...

I want to calculate the fist and last date of current month....

Iris said...

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-2,@Today))

this one is not correct when @Today = '04/29/2009'

if @Today = '04/28/2009' it will be correct.

Ashley said...

WoW nice blog buddy!Actually WoW is my favorite appreciation keyword. I also like to play. Wow Gold. I was looking for info regarding that and saw your website. Its nice and very interesting.

Mind said...

Nice post

Mind said...

buy Aion Kinah

Good one

T Labiche said...

This was exactly what I needed. Thanks.

Mind said...

wow gold kaufen

Interesting post

love said...

But This does not work for the February Months.
Is there any other way for this?

andre vella said...

Does not work dude

Alexander said...

Correct sql for first day of previous month (change first -1 for other months):

DateAdd( mm, -1,DATEADD(dd,-(DAY(@today)-1),@today))

vinaydonthi@gmail.com said...

Thanks Alexander
Original Post is wrong as you said.

Adam Tuliper said...

This doesnt work 100%

DECLARE @Today DATETIME
SELECT @Today = '8/31/2010'
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))

returns 8/2 not 8/1

BlueDog said...

DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1)-(DAY(@Today)-DAY(DATEADD(mm,1,@Today))),@Today)

Fixes the 8/2 instead of 8/1 issue by subtracting difference between value of @today and the following value of the last day in the next month (31 vs 30)

Kevin said...

How to get the last day of every six month of the year

Kumar said...

Doesn't seem to work reliably if your process date is an EOM date itself.

dotnet-programming-solutions said...

Good Queries.
An extensive list of queries to find the start and end dates of any months is here:
First and last days of any month in SQL
I think it helps.

Erik said...

To set the day to the first of the month (YYYY-MM-01)

dateadd(d, -day(getDate())+1, getDate()

Satyam Raguthu said...


--First Day of Current Month.

select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

select CONVERT(varchar,dateadd(MONTH,datediff(MONTH,0,getdate()),0),106)


--Last Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(month,1,getdate()))),dateadd(month,1,getdate())),106)

select CONVERT(varchar,dateadd(s,-1,dateadd(month,datediff(month,0,getdate())+1,0)),106)

Satyam Raguthu said...


--First Day of Current Month.

select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

select CONVERT(varchar,dateadd(MONTH,datediff(MONTH,0,getdate()),0),106)


--Last Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(month,1,getdate()))),dateadd(month,1,getdate())),106)

select CONVERT(varchar,dateadd(s,-1,dateadd(month,datediff(month,0,getdate())+1,0)),106)

Satyam Raguthu said...




--First Day of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)


--Last Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

jlp said...
This comment has been removed by the author.