December 2, 2011 at 4:55 pm
This is probably the fastest and shortest method for finding the last day of the month, given the year and month as integers, since it uses only a single DATEADD function call.
It works for the full range of the DATETIME datatype from 1753 thru 9999 and handles leap year correctly.
select
*,
LastDayOfMonth = dateadd(month,(12*[Year])-22800+[Month],-1)
from
(--Test Data
select [Year] = 1753, [Month] = 1union all
select [Year] = 2011, [Month] = 1union all
select [Year] = 2011, [Month] = 2union all
select [Year] = 2011, [Month] = 3union all
select [Year] = 2011, [Month] = 4union all
select [Year] = 2011, [Month] = 5union all
select [Year] = 2011, [Month] = 6union all
select [Year] = 2011, [Month] = 7union all
select [Year] = 2011, [Month] = 8union all
select [Year] = 2011, [Month] = 9union all
select [Year] = 2011, [Month] = 10union all
select [Year] = 2011, [Month] = 11union all
select [Year] = 2011, [Month] = 12union all
select [Year] = 2012, [Month] = 1union all
select [Year] = 2012, [Month] = 2union all
select [Year] = 9999, [Month] = 12
) a
Year Month LastDayOfMonth
----------- ----------- -----------------------
1753 1 1753-01-31 00:00:00.000
2011 1 2011-01-31 00:00:00.000
2011 2 2011-02-28 00:00:00.000
2011 3 2011-03-31 00:00:00.000
2011 4 2011-04-30 00:00:00.000
2011 5 2011-05-31 00:00:00.000
2011 6 2011-06-30 00:00:00.000
2011 7 2011-07-31 00:00:00.000
2011 8 2011-08-31 00:00:00.000
2011 9 2011-09-30 00:00:00.000
2011 10 2011-10-31 00:00:00.000
2011 11 2011-11-30 00:00:00.000
2011 12 2011-12-31 00:00:00.000
2012 1 2012-01-31 00:00:00.000
2012 2 2012-02-29 00:00:00.000
9999 12 9999-12-31 00:00:00.000
Based on code from this thread:
Converting Year, Month, and Day to DateTime
The functions on this link will take input parameters of Year, Month, and Day and return a datetime.
Make Date function (like in VB):
December 2, 2011 at 5:00 pm
@michael-2 Pretty slick script!:cool:
Thanks for posting.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 2, 2011 at 9:36 pm
-Syd- (12/2/2011)
Here is what you need in your store procedure
declare @uptoMonth as int
declare @year as int
declare @LASTDAY VARCHAR(10)
declare @lear as int
set @leap = (select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime))) when 2 then 1 else 0 end)
IF @uptoMonth IN (1,3,5,7,8,10,12)
BEGIN
SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-31'
END
IF @uptoMonth IN (4,6,9,11)
BEGIN
SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-30'
END
IF @uptoMonth = 2
BEGIN
IF @leap = 1
BEGIN
SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-29'
END
ELSE
BEGIN
SET @LASTDAY = CAST(@year as varchar(4))+'-'+CAST(@uptoMonth as varchar(2))+'-28'
END
END
Just show the variable @LASTDAY and its done!
Syd, take a look at the set-based methods that Jeff Williams and Michael Valentine Jones posted. Lot's less code, as well and no special calculation required for Leap Years.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2011 at 11:19 pm
declare @year int
declare @uptomonth int
declare @Date datetime
set @year=2011
set @uptomonth=12
set @date=convert(datetime,convert(varchar(10),@year)+'-'+convert(varchar(10),@uptomonth)+'-'+'01')
select dateadd(dd,-1,dateadd(mm,1,@date))
December 2, 2011 at 11:20 pm
declare @year int
declare @uptomonth int
declare @Date datetime
set @year=2011
set @uptomonth=12
set @date=convert(datetime,convert(varchar(10),@year)+'-'+convert(varchar(10),@uptomonth)+'-'+'01')
select dateadd(dd,-1,dateadd(mm,1,@date))
December 3, 2011 at 6:16 am
select dateadd(DD,-1,dateadd(MM,@uptomonth,cast(str(@year,4)+'-01-01'as date)))
Tom
December 3, 2011 at 9:58 am
Jeffrey Williams 3188 (12/2/2011)
Select dateadd(month, datediff(month, -1, cast(@year as char(4)) + '-' + cast(@month as varchar(2)) + '-01'), -1)
Jeff,
I missed your post. Very nice.:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 3, 2011 at 7:50 pm
Just a point of interest... Michael is correct. Anything you can do to stay away from character based conversions on these types of calculations will increase the performance of those calculations.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2011 at 10:46 pm
Jeffrey Williams 3188 (12/2/2011)
Select dateadd(month, datediff(month, -1, cast(@year as char(4)) + '-' + cast(@month as varchar(2)) + '-01'), -1)
That code is sensitive to the setting of DATEFORMAT. In the following example, it gives January 31 as the last day of the month for every month.
set dateformat ydm
select
*,
LastDayOfMonth =
dateadd(month, datediff(month, -1, cast([Year] as char(4)) + '-' + cast([Month] as varchar(2)) + '-01'), -1)
from
(--Test Data
select [Year] = 2000, [Month] = 2union all
select [Year] = 2011, [Month] = 1union all
select [Year] = 2011, [Month] = 2union all
select [Year] = 2011, [Month] = 12union all
select [Year] = 2012, [Month] = 1union all
select [Year] = 2012, [Month] = 2union all
select [Year] = 9999, [Month] = 12
) a
Results:
Year Month LastDayOfMonth
----------- ----------- -----------------------
2000 2 2000-01-31 00:00:00.000
2011 1 2011-01-31 00:00:00.000
2011 2 2011-01-31 00:00:00.000
2011 12 2011-01-31 00:00:00.000
2012 1 2012-01-31 00:00:00.000
2012 2 2012-01-31 00:00:00.000
9999 12 9999-01-31 00:00:00.000
December 4, 2011 at 3:59 am
Michael Valentine Jones (12/2/2011)
This is probably the fastest and shortest method for finding the last day of the month, given the year and month as integers, since it uses only a single DATEADD function call.It works for the full range of the DATETIME datatype from 1753 thru 9999 and handles leap year correctly.
Yes, but in this day and age shouldn't we be doing something that will work with the full range of the DATE type? it's easy enough to adapt this code to so so, just requires a double cast on that -1 to get it from INT to DATE.
So, in the form that would be needed in a SP with the two specified parameters, it would be
select DATEADD(MM,(12*@year)-22800+@uptomonth,CAST(CAST (-1 as DATETIME)as DATE))
edit: tried to get the coloring right with this somewhat disfunctional tag - but failed
Tom
December 4, 2011 at 7:01 am
Michael Valentine Jones (12/3/2011)
Jeffrey Williams 3188 (12/2/2011)
Select dateadd(month, datediff(month, -1, cast(@year as char(4)) + '-' + cast(@month as varchar(2)) + '-01'), -1)That code is sensitive to the setting of DATEFORMAT. In the following example, it gives January 31 as the last day of the month for every month.
set dateformat ydm
select
*,
LastDayOfMonth =
dateadd(month, datediff(month, -1, cast([Year] as char(4)) + '-' + cast([Month] as varchar(2)) + '-01'), -1)
from
(--Test Data
select [Year] = 2000, [Month] = 2union all
select [Year] = 2011, [Month] = 1union all
select [Year] = 2011, [Month] = 2union all
select [Year] = 2011, [Month] = 12union all
select [Year] = 2012, [Month] = 1union all
select [Year] = 2012, [Month] = 2union all
select [Year] = 9999, [Month] = 12
) a
Results:
Year Month LastDayOfMonth----------- ----------- -----------------------
2000 2 2000-01-31 00:00:00.000
2011 1 2011-01-31 00:00:00.000
2011 2 2011-01-31 00:00:00.000
2011 12 2011-01-31 00:00:00.000
2012 1 2012-01-31 00:00:00.000
2012 2 2012-01-31 00:00:00.000
9999 12 9999-01-31 00:00:00.000
This can be corrected with a little change - but, it requires more functions:
SELECT dateadd(month, datediff(month, -1, cast([Year] As char(4)) + right('0' + cast([Month] As varchar(2)), 2) + '01'), -1)
I will say I like Michael's solution better though - simpler and better performing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 4, 2011 at 10:30 am
I used a variation of the code in numerous places provided to detemine the last day of the current month.
SELECT dateadd(month, datediff(month, -1, cast(DATEPART(yyyy, GETDATE() )
As char(4)) + right('0' + cast(DATEPART(mm, GETDATE())
As varchar(2)), 2) + '01'), -1)AS LastDayCurMonth
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 4, 2011 at 10:35 am
Post withdrawn... Books Online was totally wrong about what would happen on something and I'm testing more on the subject of datatype precedence.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2011 at 11:10 am
Post withdrawn... Books Online was totally wrong about what would happen on something and I'm testing more on the subject of datatype precedence.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2011 at 5:13 pm
bhaveshp.dba (12/2/2011)
Hello All,How can I calculate last day of month?
I have two parameter in stored procedure
create procedure
(@year int,
@uptomonth)
as
begin
....................................
end
I want to calculate last day of the Month ....
based on parameter passing in stored procedure.
If I am passing
@year =2011
@uptoMonnth =9
then ouput like '2011-09-30'
another example like
If I am passing
@year =2011
@uptoMonnth =10
then ouput like '2011-10-31'
Thanks
Bhavesh
Question no one has asked, how is this value being used in the query run in the stored procedure?
This will have an impact on how you actually want to calculate the value. Also, what is the datatyoe of the column being compared in the query?
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply