SQL SERVER - Query to Find First and Last Day of Current Month « Journey to SQL Authority with Pinal Dave

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://www.SQLAuthority.com), This was requested by my co-worker.

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 33 Comments

33 Responses to “SQL SERVER - Query to Find First and Last Day of Current Month”

on June 4, 2007 at 8:38 am Mark2073

Your SQL queries for working out dates helped so much. I was on the right track but not quite there

– Last days of previous months
on July 10, 2007 at 12:49 am paranthagan

Thanks for the information
on July 22, 2007 at 8:33 pm Chaitra

hi,

I have two tables reciept and issue.
Reciept have the foloowing details

partno date rquantity rrate
a 10/1/2005 50 15
a 15/4/2005 10 22
a 11/7/2005 40 20
b 10/6/2005 30 20
b 25/6/2005 15 22

Issue have the foloowing details

partno date iquantity irate sval lval
a 14/1/2005 10 10
a 15/5/2005 20 22
a 1/8/2005 20 22
b 11/7/2005 10 20

what i have to do is
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calcucated result in “lVal” and ’sval’ column
iquantity(irate)+iquantity(irate-rrate)
example, first we have to subtract
10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50
20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440
20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440.

and also if the date difference between dates of two tables are greater than 365 days then they have to stored in the column lval otherwise in the column sval. And this should be repeated to all items.
on July 26, 2007 at 3:59 am Paul Marshall

Good article, thanks
on July 31, 2007 at 12:14 pm govind

Hi
i am looking for the function which gives last working day of the month for the given time period

example:it should be like this

getlastdayofmonth(@start_date,@end_date)

–so it should give all the dates (last day of month) between start_date and end_date

really apreciate if anyone can help with this

thank you
on August 20, 2007 at 8:43 am Karl Manton

I have used the code you wrote as follows to find the start of the current and previous months.

{
declare @mydate datetime
select @mydate = getdate()

SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(@mydate)-1),@mydate),120) as ‘StartDate’

SELECT CONVERT(varchar(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate)),120) as ‘EndDate’
}

However to use these I need them to be in date format with the times represented as 00:00:00:000 irrespective of what time of day the select statements are run.

Is this possible ?
on August 24, 2007 at 6:11 am daBroef

Hi all

The folowing piece of code gets the first day of the next month. so if you have 20 january the function finds 1 february as result.

DECLARE @datum DATETIME
SET @datum=’2007-01-31′

DECLARE @datFuture DATETIME
SET @datFuture = dateadd(mm,1,@datum)

DECLARE @dateFirstDayNewMonth datetime
SET @dateFirstDayNewMonth = cast(year(@datFuture) as varchar(50)) + ‘-’ + cast(month(@datFuture) as varchar(50)) + ‘-1′

print @dateFirstDayNewMonth
on October 11, 2007 at 3:14 am Veron

Hi. How do I get/print all the months between 2 dates, eg. 01 Aug 2006 and 31 March 2007? Eg.

Aug 2006
Sep 2006
Oct 2006
Nov 2006
Dec 2006
Jan 2007
Feb 2007
Mar 2007
on October 26, 2007 at 7:04 am bumface

what about first and last working day of month?
on December 26, 2007 at 6:05 pm Mamtha

Hi

I have to create a query where the month we are selecting must match with the current month.
Like if i select January and in database if there is 2 set of data , January and December, it has to select january data and display.
on January 22, 2008 at 6:50 pm Thiyagu

hi…
I am having problem on finding a first day of a month.. plz help me..
send me a set of codings..
on January 22, 2008 at 9:19 pm Chris Bowie

How do you find the first full week of the month?
on February 5, 2008 at 10:03 am suhan

Hi

I am looking for a query, which get me week’s first day and same week’s last day…
please help me out

Thanks and Regards
on February 7, 2008 at 6:22 pm Thiyagu

hi…
I am having problem on finding a first day of a month.. plz help me..
send me a set of codings..
on February 25, 2008 at 10:52 am Kumart

Hi,

How to get the last date of sunday of everymonth..plz help me….
on March 26, 2008 at 4:31 pm Suruchi Jain

Hey Pinal,

Your queries helped a lot
Thanks :)

Cheers
Suruchi
on April 11, 2008 at 1:00 pm ameen

hi…
it s very useful to me…………thanx…….
i have a problem that…..how can i display the group of date which should fall under 1st april n 31st of april ie one full month …like wise all the months…….??
can u help me pls???
on April 25, 2008 at 4:15 pm vasuk kuchroo

use this simple query to get first date of last month

select cast(cast(datepart(month, dateadd(mm, -1, getdate())) as varchar) +
‘/’ +
cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))
as varchar) + ‘/’ +
cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)

Use this to get previous months last date

SELECT convert(varchar,DATEADD (day, - datepart(dd,getdate()), getdate()),101)
on May 8, 2008 at 5:06 pm Ritesh S.

Thank you for the query. :)
on June 21, 2008 at 12:10 am J Liu

Thanks for the query! It saved me tons of time.
on July 1, 2008 at 8:12 am mad

hi,good day…

thanks for ure codings above….but iam having problem to select the month and display the details…eg…..i have a table with the field which would have the date,month and year (fldMonth), if let say i select the year 2007 in another drop down list, i want the table to display the details starting from june 2007 till july 2008…..

i really have no idea…plz help me out…urgent…!!!currently my coding just display the details for that particular year onli from january to december…..

thanks lots
on July 16, 2008 at 11:12 pm Brett Jones

Worked like a charm and saved a few brain ticks from figuring it out myself.

I used it for a financial report so I went a step further and did this for previous year dates. I also need to find previous year WTD so I used this:
DATEADD(wk, DATEDIFF(wk, 6, dateadd(yy,-1,@dToday)), 6)
on August 6, 2008 at 5:40 pm Imran

How to get first and last working day of month? I mean exact DAY of week when we start working by excluding SATURDAY and SUNDAY if it comes on 1st or 2nd. So Query should return 3rd.
on August 8, 2008 at 2:17 am Zelalem

/*To find first day of previous/next month*/
declare @testdate datetime
select @testdate=getdate()
–Find first day of pevious month
SELECT DATEADD(dd,1-day((DATEADD(mm,-1,@testdate))),DATEADD(mm,-1,@testdate))

–Find first day of next month
SELECT DATEADD(dd,1-day((DATEADD(mm,1,@testdate))),DATEADD(mm,1,@testdate))
on August 13, 2008 at 4:21 am nir

i have to store in the sql server database like 2008AUG how to do that
on August 13, 2008 at 10:09 am Imran Mohammed

@nir

use master
create database “2008AUG”
– give your options

use double quotation around the name of the database and you can create a database name starting with a letter.

Thanks,
Imran.
on September 8, 2008 at 7:08 pm Mohamed Idhris

hi I want to get the last day of the month between the two given dates. also I want to get the last day of the week between the two dates.
on September 24, 2008 at 1:09 am Chris Ferguson

Hi I’m trying to retrieve data from a database which uses SQL, how do I specify which dates I require values for in the DD/MM format
on October 23, 2008 at 7:57 pm Adaline

Thanks for the article, gave me clue for my requirements, I have jotted down calculating various days like this day last year, first day of prior years and so on, hope this would be useful.

–This week start date Monday –
select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)
–This week enddate–
–select dateadd(d,-1,getdate())

select cast(floor(cast(dateadd(d,-1,getdate()) as float)) as datetime)
–Last week start date–
select DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)
–Last week end date–
select DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)
–First day of this year–
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
–Last day of the prior year–
select cast(floor(cast(dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as float)) as datetime)

–First day of next year–
select DATEADD(yy, DATEDIFF(yy,-1,getdate()), 0)
–First day of prior year–
SELECT DATEADD(yy, DATEDIFF(yy,0,dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))), 0)

–This week last Year start date Monday –
select cast(floor(cast(dateadd(wk,datediff(wk,0,dateadd(yy,-1,getdate()) ),0)as float)) as datetime)
–This day last year
select dateadd(yy,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))
–First day of the current month–
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
–Last day of the current month–
select cast(floor(cast(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))as float)) as datetime)
–First day of the current month Last year–
select dateadd(yy,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
–Last day of the current month Last Year–
select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))as float)) as datetime)
on November 10, 2008 at 8:32 am Suman

Hi,

Thnaks for your query, Is it possible that instead of sending date can I send only month. Could you please send me that query if possible.

Thanks in advance.

Regards
Suman
on December 9, 2008 at 1:19 am Sara

This article helped me in one query.But i’m trying another query,can you help me in ths?

I have a Sales table data.I have user submitting every month sales data.My company has financial year from OCt 1 st to Sept 30 the next year.Whenever, i run the query, i have to get totalSalesAmount,for Salesdate which ranges between Oct 1st to till date.If i run that query now i have to get sales amount(Oct 2008+Nov 2008+Dec 2008) but when i run it in March 2009, it should should fetch (Oct 2008+Noc 2008+Dec2008+Jan 2009+Feb2009),
How do i do that?
Help appreciated
on December 11, 2008 at 6:54 pm Surya Prakash

Cool stuff.. great job keep rocking…
on December 16, 2008 at 7:43 pm Malar

can anybody help me to find out second,third,fourth,fifth tuesday,wed,thu,fri,sat,sun of a month?

thanx