Cognos Two Datetime Prompts Seconds Difference

Question

I would like to calculate seconds difference between two datetime prompt values. I would like to do it by using query calculation. I'm using DB2 and i have to use DB2 functions. I have somthing like this but it didn't work for datetime prompts, it works only like this:

(DAYS(localtimestamp) - DAYS([FIRSTOCCURRENCE])) * 86400 + 
(MIDNIGHT_SECONDS(localtimestamp) - MIDNIGHT_SECONDS([FIRSTOCCURRENCE]))

I want to use it like this :

(DAYS(?endDate?) - DAYS(?beginDate?)) * 86400 + 
(MIDNIGHT_SECONDS(?endDate?) - MIDNIGHT_SECONDS(?beginDate?))

How can i do that ? Is there anyway to do this ?

PS: localtimestamp gives Aug 20, 2014 5:26:51 PM kind of result. But prompt gives 2014-08-21T10:53:09.166. Thats the main problem i couldn't convert it.

Answer 1

Query calculation should be like this, i finally figured it out:

(hour(substring (?endTime?,1,8))*3600+minute(substring (?endTime?,1,8))*60+second(substring (?endTime?,1,8)))-
(hour(substring (?beginTime?,1,8))*3600+minute(substring (?beginTime?,1,8))*60+second(substring (?beginTime?,1,8)))+_days_between (?endDate?,?beginDate?)*86400