how to run report till current_date, when date is like 20111117

[arroju_venkat]

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.

[HalfBloodPrince]

What is the data type of the date column character or it is numeric ?

[arroju_venkat]

Date column datype is nVarChar

[HalfBloodPrince]

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

[blom0344]

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

[pricter]

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))

[blom0344]

Ah, very good indeed!!    ;)

[arroju_venkat]

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))

[pricter]

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.