Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.
I have answered this question twice before here:
SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Query to Find First and Last Day of Current Month
Today, we will see the same solution again. Please use the method you find appropriate to your requirement.
Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000
LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000
LastDay_NextMonth
———————–
2007-09-30 23:59:59.000
If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–
2007-08-31 23:59:59.000
Reference : Pinal Dave (https://blog.sqlauthority.com)
289 Comments. Leave new
Pinal — what a great site you’ve got! I found my answer here quickly and clearly. I’ll be back often.
Your syntax gave me exactly what I needed in my query, but I’m not clear on what the s switch/function is in the statment ((DATEADD(s,-1,DATEADD) and I’d like to udnerstand it so I can apply it in the future.
Thanks!
Katie
Dear Murali,
datediff()- Returns the number of date and time boundaries crossed between two specified dates.
you have given datediff(mm,0,getdate())
‘0’ is nothing in datediff function. so it’s throwing an garbage value.
You have to give some date instead of 0.
datediff gives numeric data.
Hai Pinal
i have a problem here to get the last day of month..
let say i have table A that have field month and year only…
how to select this table based on 2 parameter (month and year) to get last day of that month and year…
thanks
declare @month int, @year int
select @month=3,@year=2008
select dateadd(year,@year-1900,dateadd(month,@month,0)-1)
Madhivanan,
This query is returning 2008-02-28 if i set @month as 2, where in it should actually return 2008-02-29 as this is leap year.
Madhivanan,
This query is 2008-02-28 if i set @month as 2 where in it should actually show 2008-02-29 as its a leap year.
Try this code
declare @month int, @year int
select @month=2,@year=2008
select dateadd(year,@year-1900,dateadd(month,@month,0))-1
Sir,
I want how to search the information based on giving a parameter
Where col=@parameter_value
declare @var varchar(10)
set @var=’fn_sqlvarbasetostr’
select * from sysobjects where name like ‘%’+@var+’%’
Hi Dave,
I want to get datetime in form of yyyymmdd from stored proc. Can u guide me for this. Thanks in advance
If you use front end application, do the formation there
Otherwise
select convert(char(8),getdate(),112)
Now everyone, please, read this. Actually, read it twice! :)
Never, ever do formatting anywhere but in UI layer. If you follow MVC model, View is the place for that. And why is this?
I worked for two years in a project where things were formatted in Model, View and in Controller layers. I jumped in in the support phase so all the code was already written. I don’t know if it was ever tested though.
But. Sometimes culture settings were changed in the ASP.NET server, I really don’t know why or who, so simple parsing from “1,234,567.89” to Decimal became awfully difficult to do because in Finland we use dots as thousand separator, not commas.
This lead into a situation where user really wanted to enter the value 1234567.89 but ended up with 1.23456789 and vice versa when I displayed values I got from a Web Service.
So I had three layers where formatting and casting could, and did, go wrong and the program wasn’t the tiniest one. So I spent months in fixing just currency string currency conversions!
Also the program was written in VB.NET and did not have OPTION STRICT set ON. This lead to some interesting situations with implicit castings from string to int/double and back to string.
How all this could have been avoided? By keeping the currency values in Decimal data type everywhere in the program and formatting would have been done only when displaying them to the user / reading user input.
use ‘ like’ operator
I may be asking a very stupid question however I need to turn the output of your above statement that finds the last day of a specific month into a “YYYY-MM-DD” format with no time and use in a stored procedure query. Trying to do a CONVERT(DATETIME, @variable, 102) doesn’t seem to be doing it. Maybe I’m not thinking clearly but this seems to have worked for everything else. Any ideas?
use this and you will get select CONVERT(DATE, GETDATE(), 102)
Nevermind, I got it. I had to use a CONVERT(CHAR, @variable, 102) instead of DATETIME and it works perfect. Thanks for the information about the last day of each month.
Hi
I am new in SQL query writing.
please help me in writing the query to convert today (system date and time) to EPOCH format seconds.
I would like to use this query along with my existing helpdesk tool that stores dates in EPOCH (10 digit integer format).
Regards
Rajesh Agnihotri
How to find the last date of given year’s month.
See if this helps
declare @month int, @year int
select @month=3,@year=2008
select dateadd(year,@year-1900,dateadd(month,@month,0)-1)
Can anybody help me how to find first,second,third,fourth last mon,tue,wed,thur,fri,sat,sun of a month(In datetime or date format.).I hav a program scheduled to be run like this,I need to find next run date .
Thanks……..
how to find last monday of month? any ready functions please……….
Thanks……….
Try this
SELECT DATEADD(week, DATEDIFF(week,0,dateadd(month,datediff(month,0,GETDATE())+1,0)), 0)
SELECT DATEADD(week, DATEDIFF(week,0,dateadd(month,datediff(month,0,’2/28/2010′)+1,0)), 0) This works fine as long as the last day of the month is not a Sunday. I too am looking to find a date fuction to find the Last Monday of the month.
Our developer figured this out. Will work if Sunday is the last month and Leap Year:
DECLARE @monthOfInterest smallDateTime
DECLARE @dayOfInterest tinyint
SET @monthOfInterest = ‘2/29/2012’
SET @dayOfInterest = 5
SELECT
DATEADD(day, -(7 + DATEPART(weekday, ( DATEADD(day, -DATEPART(day, DATEADD(month, 1, @monthOfInterest)), DATEADD(month, 1, @monthOfInterest)) )) – @dayOfInterest)%7,
DATEADD(day, -DATEPART(day, DATEADD(month, 1, @monthOfInterest)), DATEADD(month, 1, @monthOfInterest)) )
This is great, thanks! Only thing when copying note that:
…@monthOfInterest)) )) – @dayOfInterest)%7,….
Should be a Minus sign in there but if you direct-copy it, it is something slightly longer than a minus sign, so just replace.
alter function fn_lastMonday
(
@date smalldatetime
)
returns smalldatetime
As
Begin
declare @lastdate datetime
set @lastdate =DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0))
return DATEADD(d,-(DATEPART(weekday,@lastdate)-1),@lastdate)
End
For Testing
declare @date smalldatetime
set @date=’2010-12-05′
select dbo.fn_lastMonday(@date),DATENAME(dw,dbo.fn_lastMonday(@date))
Last Day of Previous month I always used:
dateadd(dd, -Day(GetDate()), GetDate())
or (to strip off the time component):
convert(char(10), dateadd(dd, -Day(GetDate()), GetDate()), 101)
Is there any reason two calls to GetDate() would be an issue here? I believe they will return the exact same time. If that is the case then this method seems a lot more intuitive, simply go back the number of days you are into the current month. If it is the 5th, go back 5 days which puts you on the last day of the previous month, regardless of how many days that month contains (leap year, 30 vs. 31). GetDate returns a full datetime so it works for any right up to the stroke of midnight.
Of course, my above comment works only when you only care about the date part. Being in the BI world, I work in dimensions of dates and ranges of time. You can change Dave’s solution to:
SELECT DATEADD(ms,-2,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
to get all the way up to midnight (“s, -1” leaves the second before midnight out, and using “ms, -1” rounds to the next day since datetime is accurate to roughly 3 milliseconds.
But in those cases I highly recommend using less than the first of the month not between x and this EOM value. Any time that SQL can represent up until midnight will be caught then.
Select *
from Orders
where OrderDate >= @FirstOfLastMonth and OrderDate < @FirstOfThisMonth — not <=
From a mathematical and linear time aspect, I believe that would be more correct.
This demonstrates what I think is one of SQL Servers biggest deficiencies; the lack of a simple date data type with no time component.
Hi,
I want to get the number of days in last 21 months from now.
select datediff(day,dateadd(month,-21,getdate()),getdate())
Dave, thanks its helped me alot.
Thanks Dave, your programming is very helpful.
Very helpful. Thanks for sharing!
Hi Dave,
Can someone tell me how to get previous year previous month Date from todays date? I need to know, how we will get 02/29/2008.
Thanks in advance.
-Peter
Previous year, previous month last date
select dateadd(month,datediff(month,0,dateadd(year,-1,getdate())),-1)
No need to use DateDiff. Inside Dateadd we can use another DateAdd .
Eg. SELECT DATEADD(DD,-1,DATEADD(M, 2,GETDATE()))