t-sql first day of previous month

My Learning: Log in Learning Report [Log in]


Remove Ads or Log in
Remove Ads or Log in
+0  |  -0
If you are after T-SQL First Day of Previous Month calculation then you are in the right place.

To return first day of previous month using T-SQL we can use the following statement:

SELECT DATEADD(m,-1, Dateadd(d,1-DATEPART(d,getdate()),GETDATE())) AS [First Day of Previous Month]

Below is explanation how the t-sql statement calculates first day of previous month:

First Day of Previous month
1) Our today's date is 20th of Feb. We substract number of days in the month from our date (20) and we get last day of previous month.
2) We add one day to get first day of current month.
3) We go one month back so first day of the month stays the same and Feb changes to Jan.

The result of this T-SQL statement is first day of previous month.

Hope that helps!
Katie

Remove Ads or Log in

We need you!

By becoming a proud member we can:
  • Switch off annoying ads
  • Create more articles and videos
  • Develop more useful features supporting your learning
  • Provide FREE webinars (for members)
  • Feel our hard work since 2011 is appreciated
Become a proud member and get exclusive features.

If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website. We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.

Thanks to members:
  • Education is for FREE and we create more FREE articles and videos
  • We provide new features (members) that can speed up learning.
  • 53% of our page views (82,000 each month) do not have page ads.
  • We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only. Become a proud member

Visitor Voting

Did you know we (Katie & Emil) immigrated to UK in 2005 without knowing English, having any IT skills (or degree) and we set up a website in 2008 to learn by teaching?
Visitor Voting: Yes +13  |  No -6
Add comment (no email required).



Add Comment
Name: Ram
Date Posted: 12/9/2011 10:14:14 AM
Comment: Helpful Query.. Nice explanation also.. :)


Name: liz
Date Posted: 12/15/2011 7:21:38 PM
Comment: Thank you Katie! This great and the graphics are helpful as well!


Name: Daisy
Date Posted: 1/11/2012 1:33:57 PM
Comment: It was confusing allot


Name: deepak
Date Posted: 2/9/2012 11:29:55 AM
Comment:

 The below code is simpler :

Select DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0) as FirstDayPrevMonth
 




Name: Emil
Date Posted: 2/10/2012 7:23:30 PM
Comment:

Hi deepak. I admit your sql is shorter but I'm not convinced to it as it relies on data type minimum value to get the correct result. It should always work even in future SQL releases as I don't think Microsoft will change starting date minimum value to something else than 1st of January.

Thanks for sharing your alternative code to get first day previous month




Name: Sarikonda k suryam raju
Date Posted: 2/24/2012 8:20:02 AM
Comment:

Respected sir/madam,

                    This is suryam raju from banglore. This is long way to find the last month first day so please send me the other way to find it.




Name: Imtiyaz
Date Posted: 3/20/2012 11:50:29 AM
Comment:

This is also working...:)

select DATEADD(dd,-DAY(getdate())+1, getdate()) -- First day of current month

select DATEADD(dd,-DAY(getdate()),DATEADD(mm, 1,getdate())) -- Last day of current month

select DATEADD(dd,-DAY(getdate())+1,DATEADD(mm,-1,getdate())) -- First day of previous month

select dateadd(mm,-1,DATEADD(dd,-DAY(getdate()),DATEADD(mm,1,getdate()))) -- Last day of previous month

 




Name: Emil
Date Posted: 3/24/2012 9:05:46 AM
Comment:

Hi Imtiyaz

I'm afraid 3 out 4 of your queries contain bugs. Run scripts below with date I chose and see if it gives correct result.

declare @Date as date = '2012-02-29'

select dateadd(mm,-1,DATEADD(dd,-DAY(@Date),DATEADD(mm,1,@Date))) -- Last day of previous month

set @Date = '2012-03-31'

select DATEADD(dd,-DAY(@Date),DATEADD(mm, 1,@Date)) -- Last day of current month

select DATEADD(dd,-DAY(@Date)+1,DATEADD(mm,-1,@Date)) -- First day of previous month

 




Name: Yesmahesh
Date Posted: 11/19/2012 1:17:16 PM
Comment:

Hi Katie and Emil.. The one u posted was more helpful and thought provoking.. Thanks to that.. i had also put some stuff here which i think was error-free.. Please correct me if the following code has bugs..

--FirstDayCurrentMonth--

Select dateadd(day, -datepart(day,getdate())+1, getdate())

--LastDayCurrentMonth--

declare @date datetime=dateadd(day, -day(getdate())+1, getdate())

select dateadd(mm,1,dateadd(day, -1, @date))

--FirstDayPreviousMonth--

select dateadd(day,-day(dateadd(month, -1, getdate()))+1, dateadd(month, -1, getdate()))

--LastDayPreviousMonth--

declare @date datetime=dateadd(day, -datepart(day,getdate())+1, getdate())

select dateadd(day, -1, @date)




Name: Emil
Date Posted: 11/19/2012 8:04:56 PM
Comment:

Hi Yesmahesh

I had a quick look and your code looks good to me.... well done & thanks for sharing.

One comment I have is for LastDayPreviousMonth where you have + 1 and then -1 should cancel each other so can be simplified a little bit.

Take care

Emil




Name: YesMahesh
Date Posted: 11/27/2012 7:12:07 AM
Comment:

Yes, i found it bit complex and should be simplified.. Here i am having a simplified code to find out the Last Day Of Previous Month..

Select dateadd(day, -day(getdate()), getdate())

I hope this will work.. Please correct me if i was wrong..




Name: Emil
Date Posted: 11/27/2012 7:24:30 PM
Comment:

Hi YesMahesh,

I think you posted last day of previous month. This articles is about first day of previous month. There is another articles about last day of previous month that has very similar code to yours.

Regards

Emil




Name: Joe
Date Posted: 12/16/2012 10:42:56 PM
Comment:

Thanks guys, saves me askin the ever so large headed so called developer for this !




Name: Sinisa
Date Posted: 2/24/2013 6:38:27 PM
Comment:

Get the first day of th ecurrent month using convert to varchar:


select convert(date,   convert(varchar(8),getdate(),121)+'01') ;




Name: Coco
Date Posted: 4/2/2013 4:33:13 PM
Comment:

Thank you for this post!  You guys are awesome!




Name: Senthilnathan
Date Posted: 5/14/2013 7:11:27 AM
Comment:

Fantastic Explanation ............. For date Calculation




Name: Husain
Date Posted: 5/26/2014 4:29:39 PM
Comment: A better query - select DATEADD(D, -DAY(GETDATE()) +1 , GETDATE())


Name: Emil
Date Posted: 5/26/2014 4:46:50 PM
Comment: Hi Husain, Thanks for sharing and this would be correct for current month however our code calculates first day of previous month. Take care Emil




Are you a member?

If yes than please log in

Learning Map is for members with Bronze, Silver and Gold Membership.
For more information visit membership options.


Close this window , log in or learn more about membership benefits
  Become a Proud Member