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
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
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...
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());
it worked..
thanks