Date object doesn't work in query filters

[Former Member]

I'm trying to create a universe object called Next Biweekly Pay End Date. Some background: there is an end date for our biweekly payrolls every 14 days, and the logic behind the object is basically that if there was a biweekly pay end date 7 days ago, the next one is 7 days from today (that is, sysdate + 7), etc.</p>

I have it working so that it displays the correct date when used in a WebI report. However, when I use it in a query filter in a WebI report (like, Pay End Date = Next Biweekly Pay End Date), I don't get an error, but I also don't get any data. Somehow my object doesn't work in a query filter. The purpose of the object is for use in query filters, so I need to get this to work.</p>

My best guess about why it doesn't work in a query filter is due to some issue with the data type. Pay End Date has a data type of date in the universe. I gave Next Biweekly Pay End Date a data type of date also, but the query filter still doesn't work; I get "no data to fetch."</p>

We're using XI 3.1.6 and Oracle 10. Any ideas about how to get this to work?</p>

If it helps, here is the definition of Next Biweekly Pay End Date. The logic is that it takes today's date, subtracts Oct. 31, 2009 (a biweekly pay end date), divides by 14, and looks at the remainder. If the remainder is 0, then today is a pay end date and the next one is 14 days from now. If the remainder is 1, yesterday was a pay end date and the next one is 13 days from now, etc.</p>

case</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 0 then (sysdate + 14)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 1 then (sysdate + 13)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 2 then (sysdate + 12)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 3 then (sysdate + 11)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 4 then (sysdate + 10)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 5 then (sysdate + 9)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 6 then (sysdate + 8)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 7 then (sysdate + 7)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 8 then (sysdate + 6)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 9 then (sysdate + 5)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 10 then (sysdate + 4)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 11 then (sysdate + 3)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 12 then (sysdate + 2)</p>
  when mod((trunc(sysdate)) - (to_date('31/10/2009')),14) = 13 then (sysdate + 1)</p>
end

[Former Member · Accepted solution]

Thanks for the suggestions.

I did some more testing, and there seems to be something more complicated going on. I ran the following code in SQL*Plus:

SELECT distinct M_PYDW1.PYSTATUS.DW_OWNER,

case

when to_date(sysdate, 'dd/mm/yyyy hh24:mi:ss') >= to_date('01/01/2010','dd/mm/yyyy hh24:mi:ss') then To_Date((sysdate - 2),'dd/mm/yyyy')

else to_date(sysdate +2, 'dd/mm/yyyy')

end

FROM M_PYDW1.PYSTATUS

The condition should be true, because the sysdate (today is Jan. 20) is greater than 1/1/2010, so I should get a result of sysdate - 2 (Jan. 18). But the result I get is Jan. 22 (sysdate +2). It seems that the comparison is failing even though I'm formatting both sysdate and 1/1/2010 as dates with a timestamp. What could cause that?

One more mystery: when I run the same code as above in a WebI report (using custom SQL), I get the error ORA-1830, date format picture ends before converting entire input string.

[Former Member · Accepted solution]

Hi,

To_Date() function convert string or chars to date while SYSDATE returns the date type object. This may be the reason why the comparison is not happening correctly. Also the second parameter in the To_Date() is the format of the input string.

 to_date('20020315', 'yyyymmdd') would return a date value of Mar 15, 2002 

To get rid of the "date format picture ends before converting entire input string." error use To_Char() function first before using To_Date().

Hope this helps you.

Regards,

Rohit

[Former Member]

Thanks, Rohit. When I use to_date(to_char ()), the result is of date type and works in a query filter.

[Former Member]

Hi,

1.Check the format of sysdate and Next Biweekly Pay End Date

2. Is there any time stamp for SYS date ?

Cheers,

Suresh Aluri.

[former_member207342]

1. Check the datatype and format of "Next Biweekly Pay End Date"

2. Check the format of the other operand with which you are comparing.

--Kuldeep

[Former Member]

Hi,

Check whether the problem is because of timestamp comparison in the query filter.

In your formula you are using trunc to remove tmestamp but is there any timestamp present in pay end date.

Gaurav