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

On Tue, 26 Jun 2007 23:47:24 +0100, Namwar Rizvi wrote:

(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.

>For many other utility functions and SQL Server information:
>
>http://blog.namwarrizvi.com

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
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

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



"Namwar Rizvi" <namwar@hotmail.com> wrote in message
news:ulEh4PEuHHA.3400@TK2MSFTNGP03.phx.gbl...
> 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
>
>
>
>
>
>
>

On Jun 26, 4:03 pm, "Adam Machanic" <amacha...@IHATESPAMgmail.com>
wrote:
> 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
>
> "Namwar Rizvi" <nam...@hotmail.com> wrote in message
>
> news:ulEh4PEuHHA.3400@TK2MSFTNGP03.phx.gbl...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

Is this what your looking for?:

declare
@date smalldatetime,
@day int,
@date_from smalldatetime,
@date_to smalldatetime

set @date = GetDate()

set @date = convert(smalldatetime, convert(varchar(10),@date,101))
set @day = datepart(dd, @date)
set @date_to = dateadd(dd, -@day, @date)
set @date_from = dateadd(m, -1, dateadd(dd, 1, @date_to))