Month end date
-
I want to output a month end date whenever I input a date (any date) to the user defined function. Is there any idea how to do?
create function getMthEndDate (@date datetime)
returns datetime
as
<coding_here>
go
Example1:
select getMthEndDate('20040329')
Result: 20040331
Example2:
select getMthEndDate('20040331')
Result: 20040331
Thank you in advance
Regards,
kokyan 
-
create function getMthEndDate (@date datetime)
returns datetime
as
begin
return DATEADD(d,-1,DATEADD(m,1,LEFT(CONVERT(varchar,@date,112),6)+'01'))
end
go
Far away is close at hand in the images of elsewhere.
Anon. -
Declare @Date as smalldatetime,
@Fmonth as smalldatetime,
@EomPrev as smalldatetime,
@EomCurr as smalldatetime,
@EomNext as smalldatetime
set @Date = cast(convert(varchar(15),getdate()+5, 101)as smalldatetime)
set @Fmonth = (@Date - Day(@Date)+1) -- Sets To First Day Of Current Month
set @EomPrev = @Date - Day(@Date) -- Sets to Last Day of Prev Month
set @EomCurr = (@Fmonth + 31 - Day(@Fmonth + 31)) -- Sets To Last Day Of Current Month
set @EomNext = (@EomCurr + 35 - Day(@EomCurr + 35)) -- Sets To Last Day Of Next Month
select @Date
select @Fmonth
select @EomPrev
select @EomCurr
select @EomNext
[font="Courier New"]ZenDada[/font]
-
Here's your function:
CREATE FUNCTION dbo.lastdayofmonth
(@ReportDt datetime)
RETURNS datetime
AS
BEGIN
declare @fmonth datetime
declare @lastday datetime
set @ReportDt = cast(convert(varchar(15), @ReportDt, 101) as datetime)
set @fmonth = @ReportDt - Day(@ReportDt) + 1
set @lastday = (@fmonth + 31) - day(@fmonth + 31)
return @lastday
END
[font="Courier New"]ZenDada[/font]
-
declare @date datetime
set @date = '20040329'
select dateadd(month,1+datediff(month,0,@date),0)-1
set @date = '20040331'
select dateadd(month,1+datediff(month,0,@date),0)-1
--/rockmoose
/*
CREATE FUNCTION dbo.getMthEndDate(@date datetime)
RETURNS DATETIME
AS
BEGIN
RETURN dateadd(month,1+datediff(month,0,@date),0)-1
END
*/
You must unlearn what You have learnt -
That's snazzy. I'm keepin' that one!
[font="Courier New"]ZenDada[/font]
-
Thank you. I prefer David Burrows method - neat!
Regards, kokyan -
The two methods basically use the same technique:
Find the first day of next month and substract one.
The method that doesn't use string manipulation is slightly faster. Which of course has no importance unless the number of times the algorithm runs is ridiculously large.
/rockmoose
You must unlearn what You have learnt -
Nice one roockmoose
Knew there was a way without convert but could not remember it
Far away is close at hand in the images of elsewhere.
Anon. -
Hi Dave,
long time, no see
Knew there was a way without convert but could not remember it
Showing your age?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Showing your age? Ouch!
long time, no see And you, thought you had retired
Far away is close at hand in the images of elsewhere.
Anon. -
Behold, that would mean spending the whole day with the kids! No, no
I just discovered, that posting to the german SQL Server MS newsgroup is a lot of fun ...and helps promoting my website. That's all.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
"The method that doesn't use string manipulation is slightly faster. Which of course has no importance unless the number of times the algorithm runs is ridiculously large."
Exactly - that's why I'm keeping the Rockmoose method. I usually have to do this on tens or hundreds of thousands at a time.
[font="Courier New"]ZenDada[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply