Filter to get last day of current month

[phanicrn]

i have select statement whcih gets last day of current month, but how do i write a fitler in report studio
select * from EDW.VW_Calendar
where
"----
Calendar_Date >=dateadd(mm,datediff(mm,-1, getdate()),-1) and
calendar_date<dateadd(mm,datediff(mm,0, getdate())+1,0)--"


how do i write the following condition to work in report studio as a filter

[Mr.Cognos]

Hi phanicrn,

You have some functions in Report Studio for do that.

If you want filter a query with last day of month, you can apply this filter:

Calendar_Date = _last_of_month(_add_months(getdate();-1)) If your date format is 'dd/mm/yyyy' withouth hour.

If your date format contain hour, you must filter:

Calendar_Date >= _last_of_month(_add_months(getdate();-1))
AND Calendar_Date < _first_of_month(getdate())

(I supose that you run the report to month closed...)

Bye,

Mr. Cognos

[phanicrn]

when i apply this condition
"Calendar_Date >= _last_of_month(_add_months(getdate();-1))
AND Calendar_Date < _first_of_month(getdate())
" it's giving me the First day of every month, but i want Last day of every month...

[Mr.Cognos]

when you apply this condition
"Calendar_Date >= _last_of_month(_add_months(getdate();-1))
AND Calendar_Date < _first_of_month(getdate())
If getdate return 02/07/2008 (mm/dd/yyyy)
(_add_months('02/07/2008';-1)) return '01/07/2008'
_last_of_month('01/07/2008') return '01/31/2008'
and _first_of_month('02/07/2008') return '02/01/2008'
so your query return

'01/31/2008' >= data < '02/01/2008'  so your data is for  '01/31/2008'  (Last day of previous month)
Don't this what you want?


If you want last day of current month, you only have to filter:
Calendar_Date = _last_day_of_month(getdate());

[phanicrn]

it worked..

thanks