Determining Last Day of the Month--Quick UDF -- sql server connect

Adam Machanic
6/26/2007 7:03:12 PM

This has absolutely nothing to do with ANY of the groups you crossposted to.
Please refrain from needlessly spamming in these groups--SPAM masquerading
as a "tip" is still nothing more than SPAM. If you want to advertise, go
buy some Google ads.

--

Adam Machanic
SQL Server MVP

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220

[quoted text, click to view]

Namwar Rizvi
6/26/2007 11:47:24 PM

Hi All,
Getting the last day of the month, for the given date, by TSQL is bit
tricky. Following user defined function provides you a handy general purpose
function to get the last day of the month.
It gets the last day of the month as follows:
For example we have a given date 17-March-2007

1.. Add a month in the given date by using DateAdd function. It will
become 17-April-2007

2.. Get the Day part of the date from the above step. It is 17

3.. Subtract number of days of the second step from the date of step 1. It
will become 31-March-2007
4.. Resulting date in step 3 is our answer.
Following is the TSQL code of the function

Create function
udf_GetLastDayOfTheMonth(@m_GivenDate datetime)
returns datetime
as
Begin
Return dateadd(day,-1*
day(dateadd(month,1,@m_GivenDate)),dateadd(month,1,@m_GivenDate))

End

Namwar

For many other utility functions and SQL Server information:

http://blog.namwarrizvi.com

Hugo Kornelis
6/27/2007 12:57:56 AM

[quoted text, click to view]

(snip)

Hi Namwar,

Crossposting a message to seven groups is considered rude. Especially
when your message is not on-topic in any of them.

Even more to the point, your method of finding the last day of the month
is needlessly complex.

SELECT DATEADD(month,
DATEDIFF(month, '20000101', getdate()),
'20000131');

And don't enclose it in a UDF but inline it in your queries for best
performance.

[quoted text, click to view]

If you want to advertise your blog, at least make sure to check what you
write first, and to post to appropriate groups.

--
Hugo Kornelis, SQL Server MVP

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server connect

sql server connect : Determining Last Day of the Month--Quick UDF