Filtering on end-of-time dates

[Cognoscat]

Our school uses Cognos to extract data from our ERP system, which is the Sungard Banner system.  Within the finance module and some of the other modules, for the current record, the system stores an "end-of-time" date in the end date field instead of leaving it blank until the record is ended.  When displayed in reports or on the screen, the field is displayed as DEC-31-2099 12:00:00 A.M. or just DEC-31-2099 if time is not displayed.

When writing a Cognos report and using the select value function when creating a filter, the value is stored in Cognos as 2099-12-31 T12:00:00.0000000.

I am trying to run a query that retrieves all records where the effective end date field is December 31 2099. 

I have tried various filtering techniques that I know to retrieve the December 31, 2099, records and am having problems.   I can successfully write the filter below using words like "like" and "contains" and do a test and get no errors; however, when I run my report, I get back zero records.   

[Employee].[Labor Cost Distribution].[EFFECTIVE_END_DATE] like 2099-12-31T00:00:00.000000000

I have tried putting this expression in single quotes.  I have tried filtering on like '2099%' and other options, but nothing is working.

Does anyone have any suggestions?

[MFGF]

Hi,

I often use the simplest solution I can think of. :) Have you tried

[Employee].[Labor Cost Distribution].[EFFECTIVE_END_DATE] = 2099-12-31

Regards,

MF.

[Lynn]

I thought the end of time was coming up in 2012? Guess I'll have to re-check my Mayan calendar.  :D

Anyway, I agree that it is best to find the simplest syntax for your source....but an alternative if you can't get it:

Code Select Expand

[Employee].[Labor Cost Distribution].[EFFECTIVE_END_DATE] = _make_timestamp(2099,12,31)

You are treating the value as if it is stored as a string, which it probably isn't so you likely need the proper way to express the date/time format.

[Cognoscat]

I would like to thank both MFGF and Lynn for their suggestions.  I tried both of them and both give me the results I am looking for.