Hi,
I have a date cloumn like 20111117 (=Nov 17, 2011), and the report should run till current_date.
Could you please let me know how to apply the filter.
What is the data type of the date column character or it is numeric ?
Date column datype is nVarChar
try this query calculation as sysdate in that add
cast(extract(year,sysdate()),varchar(5))+
cast(extract(month,sysdate()),varchar(5))+
cast(extract(day,sysdate()),varchar(5))
then add filter
date=sysdate
It is slightly more complex, as you need to compensate for months 1-9
date 2011-09-30 would become '2011930' instead of '20110930'
case
when month(current_date) < 10
then
(cast(extract(year,current_date),varchar(4))||
'0' ||
cast(extract(month,current_date),varchar(2))||
cast(extract(day,current_date),varchar(2))
)
else
(cast(extract(year,current_date),varchar(4))||
cast(extract(month,current_date),varchar(2))||
cast(extract(day,current_date),varchar(2))
)
end
I would also use current_date as Cognos type and || for concatenation
This can be down also without the case
cast(extract(year,current_date),varchar(4))||
right('0' ||cast(extract(month,current_date),varchar(2)),2)||
cast(extract(day,current_date),varchar(2))
Ah, very good indeed!! ;)
Thanks a lot for your quick reply....
I am using it like this, is working fine (similar to HalfBooldPrince's reply).
cast(extract(year,current_date),varchar(5))+
cast(extract(month,current_date),varchar(5))+
cast(extract(day,current_date),varchar(5))
It worked fine cause we have November and
extract(month,current_date) will return 11
on December will return 12 (so you will not have a problem)
but on January will return 1 and this will not work if your store month such as 01.
Now that I am thinking if you store days that r less than 10 like 01,02,03 etc you have to do this also for days.