I'm not sure how to take two string fields, both with date and time and get the difference. I've been looking around the forums and see how to cast a string to date YYYYMMDD but not sure how to take that one step further and include the time in that conversion.
Thoughts? Thank you, -Cort
if you are using an oracle database you can use to_date([element], 'yyyymmdd') and then use the cognos function _days_between(day1, day2) to get the number of days between them
Thank you for the quick reply. Let me give some more details:
I have two textBlob data type fields that contain comments and other information but I can use an additional field to filter and make sure that it is two dates.
OLD VALUE NEW VALUE
1970-01-01 00:00:46 1970-01-01 00:08:14
Once I have the filter showing only date values, I wanted to get the time difference between the two but am unable to convert this to datetime.
Stuck In Cognos,
Cort
so you want to display the difference as a date format? or you want to be able to compute the difference between the two date fields?
In SQL Server I'd get the difference in seconds by using the datediff() function like this:
SELECT Datediff(second,[NEW VALUE],[OLD VALUE]) as difference_in_seconds
Good luck ! ;)
My end goal is to compute the time difference (seconds or minutes) between these two text dates. So I can display that on the report.
Thanks for the help so far.
You can cast each of your strings to a TIMESTAMP data type. Then create a query calculation that subtracts the two. You will get an interval type item that displays the difference. In your example it appears as "7 minutes 28 seconds", but it will vary depending on the two timestamps. If you select the item in the layout and use the data format for interval you can customize the display.
The attached XML spec is from the GO Sales (query) sample package, but I just created two query items with your example dates hard coded as strings to illustrate.
Hope this helps!
Thank you all for the help and suggestions. My first problem was dealing with a LOB data type. I wasn't able to cast it to char or date or anything. I ended up changing the datatype in the database to string so now I am able to convert it to a timestamp to get the difference between the two dates! 8)