KEBI Academy [Log in]:
[Video: Watch or Download]

Learning Status:
Next Repetition Date: (testing)
Self Learning Report [Log in]

t-sql first day of previous month


+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

KEBI Academy is open!

You can now join our KEBI Academy and just learn more.

How much? It's voluntary payment so you decide how much you want to contribute to what you get out of it.

Join KEBI Academy

Comments



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


Visitor Voting

We are thinking about re-introducing webinars (SQL, SSIS, SSRS, SSAS) but this time for members only (due to effort).
Is that something you might be interested in?
Visitor Voting: Yes +34  |  No -7