Finding the end of month.

I am just learning MS SQL and have run onto a problem I can't find an answer
to. Is there a function to get the last day of a month in MS SQL? I am using
a "Sam's Learn SQL in 21 days" book and the SQL the author is using is not MS
SQL. He uses a function "Last_Day(field name)" ,where the "field name" is a
date field in a table. I have checked to Knowledge Base and can find nothing
there. Thanks in advance for your assistance. If you know of a better book
please let me know.

On Tue, 7 Oct 2008 09:22:01 -0700, jmillerWV wrote:

>I am just learning MS SQL and have run onto a problem I can't find an answer
>to. Is there a function to get the last day of a month in MS SQL? I am using
>a "Sam's Learn SQL in 21 days" book and the SQL the author is using is not MS
>SQL. He uses a function "Last_Day(field name)" ,where the "field name" is a
>date field in a table. I have checked to Knowledge Base and can find nothing
>there. Thanks in advance for your assistance. If you know of a better book
>please let me know.
>

Hi jmillerWV,

There is no single standard function to return the last day of a month,
but it's easy to calculate. The code below finds the last day of the
current month; you can replace CURRENT_TIMESTAMP with a variable or a
column name (when used in a query) or even an expression that results in
a datetime (or date when you're on SQL Server 2008) data type.

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

The trick is to calculate the number of months since the first day of
some "baseline" month, then add that number of month to the last day of
the same baseline month. I used january 2000 as the baseline, but you
can substitute any 31-day month.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis