Last Day of Month Function

Hello all,
I am trying to avoid using wordy logic to get date values meeting criteria
for the last day of the each month in a date field. Is anyone aware of a
last day of the month function? I know that Oracle offers this.
Thanks for your help.
J.Hi,
There is no direct function in SQL Server to get last day of the month. You
will have to use Datepart and DateAdd function to achive this.
Thanks
Hari
SQL Server MVP
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||If you use SQL Server 2000, you can write your own LastDayOfMonth(month) UDF
easily.
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||Unfortunately you have to do it yourself... Easiest way is to take one day
prior to first day of next month
Declare @.D DateTime
Set @.D = getdate()
Select DateAdd(day, -1, convert(varchar(6),
DateAdd(month,1,@.D),112) + '01')
"j" wrote:

> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||There is no such function directly, but you can use the following:
DECLARE @.dt SMALLDATETIME
SET @.dt = GETDATE()
SELECT DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd,1-DAY(@.dt), @.dt)))
Or you can use a calendar table: http://www.aspfaq.com/show.asp?id=2519
Jacco Schalkwijk
SQL Server MVP
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||Use a Calender table.
Or use dateadd and datepart:
select dateadd(d, -datepart(d, getdate()), dateadd(m, 1, getdate()))
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||Thank you everyone so very much!! ;->
"j" wrote:

> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.