using cast funtion to convert current_date into integer

[spattewar]

Hi,

Im my database the dates are stored in an integer format for e.g. for today's date they are stored as 20070515. But the current_date is in the format 2007-05-15. If I want to put a filter to the query to return rows for today's date only then how should I go about doing it. I am using cognos 8.0.

Currently I am trying
[DateLastUpdated] = cast(current_date,integer)

but it is failing and gives error saying cannot convert date2 to integer.

Thanks a lot for your help

$wapnil

 

[mfgf]

How about:

[DateLastUpdated] = year(current_date)*10000 + month(current_date)*100 + day(current_date)

Will that work for you?

MF.

 

[spattewar]

Thanks for your input.

I tried your suggestion but it gave an error saying

The function "year" is not available as an external, database, or built-in function

Is there something which I am missing???

$wapnil

 

[spattewar]

I could get it working by putting something like

(_age(cast([DateLastUpdated],date)) = 0)
AND
([TimeLastUpdated]=maximum([TimeLastUpdated] for [VendorTradeNumber]))

I do not know if it is the correct approach. Kindly provide your comments.

thanks.$wapnil