December 2, 2011 at 11:12 am
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
December 2, 2011 at 11:16 am
You have many to do this.
One of them is to create a table an fill it up with month and last day.
In the table just this two fields, MONTH and LASTDAY
MONTH - LASTDAY
1 31
2 28
3 31
4 30
ETC ETC
so in the query to get the last day just look the month of the parameter and looked on this table.
December 2, 2011 at 11:20 am
SELECT TOP 1 dt
FROM dbo.Calendar
WHERE Y = @Year AND M = @Month
--AND IsBusDay = 1
ORDER BY dt DESC
December 2, 2011 at 11:20 am
Use the month and year to get the first day of that month. Then use DateAdd to add a month to, then DateAdd again to subtract one day, and you'll have the last day of the month.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 2, 2011 at 11:20 am
This gives you last day of the current month
SELECT convert(varchar,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),101)
December 2, 2011 at 11:39 am
DECLARE @LASTDAY VARCHAR(10)
declare @uptoMonth as int
declare @year as int
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!
December 2, 2011 at 11:45 am
bhaveshp.dba (12/2/2011)
Thanks For replying me .
If year =2012 then @Last day is 29 ...then
what i need to chagne in this stored procedure.
Thanks
Works in my code.
Can then by turned into itfv to use with cross/outer apply.
Nothing wrong here with doing the math approach, untill you need the end of the month... on a week day or business dayor that is not a holiday or...
December 2, 2011 at 12:03 pm
Select dateadd(month, datediff(month, -1, cast(@year as char(4)) + '-' + cast(@month as varchar(2)) + '-01'), -1)
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 2, 2011 at 12:24 pm
Keep in mind:
A year will be a leap year if it is evenly divisible by 4 , but not 100. If a year is divisible by 4 and 100, it is NOT a leap year unless it is also divisible by 400
December 2, 2011 at 12:26 pm
Thanks You so much,,
Jeff
It;s working fine.:-):-):-)
December 2, 2011 at 1:18 pm
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!
December 2, 2011 at 2:50 pm
OK, here's a one-liner for you:
SELECT DATEPART(dd, DATEADD(dd, -1, CAST(CAST(@Month+1 AS VARCHAR(2)) + '/1/' + CAST(@Year AS VARCHAR(4)) AS DATETIME)))
...
December 2, 2011 at 3:39 pm
bhaveshp.dba (12/2/2011)
Hello Ninja's_RGR'usi tried your calander but it is not executed in my sql.
Hello Sys..
i tried your code and i want to know if @year is leap year then @Last day is 29.
So how can i do with your stored procedure.
Thanks
Bhavesh
Comment out the filegroup on the create table script. It'll work then
I've wanted to edit that out for a while but I can't find the original post and I've linked to it 15 times so I can't delete it either!
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply