Find out last day of the month

[ri58776]

Hi,
does anyone have any idea how can we get last date of the month in SQL reportging.
I am using following fomula, but it works only for 31 days of month, not for feb,april,june. ..(30 days)

=month(today()) & "/" & DatePart("d", DateAdd("d", -1, DateAdd("m", 1, DateAdd("d", -DatePart("d", today()) + 1, today())))) & "/" & year(today())

[cyberdwarf]

Hi

Using T-SQL, try this:-

[size=1]
declare @d datetime
declare @mth tinyint
set @mth = datepart(mm,getdate())+1
if @mth=13 set @mth = 1
set @d = dateadd(d,-1,convert(datetime,cast(@mth as nvarchar(2)) +'/01/2007',101))

HTH
Steve

[jai80]

Hi,

try this ...

declare @year int,@month int
declare @FirstDate datetime, @LastDate datetime
declare @MonthDays int
set @year=2006
set @month=4
set @FirstDate=conv ert(datetime, convert(varchar ,@month)+'/'+'01'+'/'+ convert(varchar ,@year) )
set @MonthDays= DAY(DATEADD(d, -DAY(DATEADD(m,1 ,@FirstDate)),D ATEADD(m,1,@Fir stDate)))
set @LastDate=datea dd(d,@MonthDays-1,@FirstDate)
select @LastDate

GudLuck

Cheers,
jai

[dhbabcock]

Hi Folks,
Just thought I'd toss this up here.

declare @ld_date datetime
set @ld_date = '2008-11-01'

select dateadd(day, (-1 * datepart(day, dateadd(month, 1, @ld_date))), dateadd(month, 1, @ld_date))

Cheers