Dan Guzman - Not the MVP (10/28/2010)
Michael Valentine Jones (10/27/2010)
Dan Guzman - Not the MVP (10/27/2010)
So, is this the simplest and quickest way to get the last day of any month?DATEADD(MONTH,x,'2010-Jan-01')
Where x is the month you are looking for the last day.
I've seen a lot of code over the years that performs some sort of find the first day of the month after the month in question, then subtract 1 day. Seems totally unnecessary with this function.
Not sure if you mis-typed that, but it is fairly obvious that will return the FIRST day of the month AFTER x. For example, if x=10, it will return 2010-11-01 00:00:00.000.
This is the standard and most efficient way of getting the last day of the month a particular date is in:
select LastDay = dateadd(mm,datediff(mm,-1,getdate()),-1)Result:
LastDay-----------------------
2010-10-31 00:00:00.000
You are correct, I meant January 31st. so:
DATEADD(MONTH,x-1,'2010-Jan-31') so that x is the month your looking for,
or for last day of the current month:
DATEADD(MONTH,MONTH(GETDATE())-1,'2010-Jan-31')
I even think
DATEADD(MONTH,MONTH(GETDATE())-1,CAST(YEAR(GETDATE()) AS VARCHAR(4)) +'-Jan-31')
is easier to read than the one you mention, with the DATEDIFF, but that's just me.
I think it’s debatable that the code you posted is easier to read, especially since is much longer. Maybe what you really mean is that you find it easier to understand. I have to disagree, especially since the method I posted is so easy to adapt to similar problems:
select Description = 'Getdate()',DT = getdate()union all
select 'Start of Day',DT = dateadd(dd,datediff(dd, 0,getdate()), 0)union all
select 'Start of Yesterday',DT = dateadd(dd,datediff(dd, 0,getdate())-1, 0)union all
select 'Start of Next Day',DT = dateadd(dd,datediff(dd, 0,getdate())+1, 0)union all
select 'First Day of Month',DT = dateadd(mm,datediff(mm, 0,getdate()), 0)union all
select 'Last Day of Month',DT = dateadd(mm,datediff(mm,-1,getdate()),-1)union all
select 'First Day of Last Month',DT = dateadd(mm,datediff(mm, 0,getdate())-1, 0)union all
select 'Last Day of Last Month',DT = dateadd(mm,datediff(mm,-1,getdate())-1,-1)union all
select 'First Day of Next Month',DT = dateadd(mm,datediff(mm, 0,getdate())+1, 0)union all
select 'Last Day of Next Month',DT = dateadd(mm,datediff(mm,-1,getdate())+1,-1)union all
select 'First Day of Quarter',DT = dateadd(qq,datediff(qq, 0,getdate()), 0)union all
select 'Last Day of Quarter',DT = dateadd(qq,datediff(qq,-1,getdate()),-1)union all
select 'First Day of Year',DT = dateadd(yy,datediff(yy, 0,getdate()), 0)union all
select 'Last Day of Year',DT = dateadd(yy,datediff(yy,-1,getdate()),-1)union all
select 'First Day of Last Year',DT = dateadd(yy,datediff(yy, 0,getdate())-1, 0)union all
select 'Last Day of Last Year',DT = dateadd(yy,datediff(yy,-1,getdate())-1,-1)union all
select 'First Day of Next Year',DT = dateadd(yy,datediff(yy, 0,getdate())+1, 0)union all
select 'Last Day of Next Year',DT = dateadd(yy,datediff(yy,-1,getdate())+1,-1)
Results:
Description DT
----------------------- -----------------------
Getdate() 2010-10-28 14:27:12.620
Start of Day 2010-10-28 00:00:00.000
Start of Yesterday 2010-10-27 00:00:00.000
Start of Next Day 2010-10-29 00:00:00.000
First Day of Month 2010-10-01 00:00:00.000
Last Day of Month 2010-10-31 00:00:00.000
First Day of Last Month 2010-09-01 00:00:00.000
Last Day of Last Month 2010-09-30 00:00:00.000
First Day of Next Month 2010-11-01 00:00:00.000
Last Day of Next Month 2010-11-30 00:00:00.000
First Day of Quarter 2010-10-01 00:00:00.000
Last Day of Quarter 2010-12-31 00:00:00.000
First Day of Year 2010-01-01 00:00:00.000
Last Day of Year 2010-12-31 00:00:00.000
First Day of Last Year 2009-01-01 00:00:00.000
Last Day of Last Year 2009-12-31 00:00:00.000
First Day of Next Year 2011-01-01 00:00:00.000
Last Day of Next Year 2011-12-31 00:00:00.000
Extensive testing has shown that date manipulations involving conversion to character strings are much less efficient than the code I posted. Also, that code does not work with settings of language that are non-english, so at the very least, you should change the code to this:
select dateadd(month,month(getdate())-1,cast(year(getdate()) as varchar(4)) +'0131')