1st of All I am not trying to post same issues twice.I am going to explain what we have been facing at work.
we got date time diff for two Max & Min column by extract(days,hour,min,sec) function.
we have a result like 15.23, 20.34, and so on..for max & min col
we tried lot to get their difference, but not able ........
we tried _days_between([data1],[data2]).....but no success yet
we are in 8.1 and with db2
please advice........
thank you for all.........in advance
So, you want the difference between two date-time values, expressed as a number of days? How should the time part be displayed - hours:mins:secs or as a decimal part of a day?
_days_between() is certainly a good place to start - what results does it give you, and how would you like them to be different?
MF.
than you for the reponse
we need the results in decimal part. but we are not getting anyhing while doing _days_between ([date1],[date2])
please advice
this is the error we are getting--
UDA-SQL-0460 A general exception has occurred during local processing. UDA-EE-0094 The operation "_days_between" is invalid for the following combination of data types: "double" and "double" RSV-SRV-0042 Trace back
Hi,
Thanks for the error message - that gives a good clue. Cognos 8 thinks your Date columns are a numeric data type, not a date data type.
Are the items you are using in the calculation real date-time columns in the database? What do you see if you write a simple report including these two items in a list?
MF.
we have a original date time format are-
may 19, 2009 11:23:10 AM
apr 15, 2009 1:08:57 PM, and so on...
then we get MAX and MIN date time by applying
businees condition ----after that we extract
(extract(day,[MInData1])*1440+extract(hour,[MinData1])*60+extract(minute,[MinData1]))/1440
and
(extract(day,[MaxData2])*1440+extract(hour,[MaxData2])*60+extract(minute,[MAxData2]))/1440
results we get---
22.67222222---MIN
26.44097222--MIN
we applied a function _days_between([Date1],[Date2])
but no results but the ERROR that I mention on last post
thank you agian...
Hi,
So are you trying to use the _days_between function on the results (22.6722222 and 26.44097222)? If so, this will not work as the _days_between function will only work with dates.
Quote from: pooja on 29 Jan 2010 09:29:38 AMthen we get MAX and MIN date time by applying businees condition
How exactly are you finding the max and min dates? Using a calculation? What is the syntax you are using?
Assuming this is still returning date-time values, have you tried using the _days_between function on the [Mindata1] and [Mindata2] items?
MF.
we get MAX n MIN by;
maximum([time] for [dept], [process])
minimum([time] for [dept],[process])
while trying _days_between([max],[min]) for
May 9,2008 12:57:10
&
June 5 2008, 3:48:11
I got 27-----but I need in decimal with calulating min:sec:msec too
please advice....
cont from last post------
my issues is...
when you have datetime like this
Sept 8, 2008 10:59:22 AM
&
Sept 8, 2008 1:41:12 PM
then _days_between() does not give anything..
please advice
As posted in an earlier post on datetime differences:
_round((extract(day;current_timestamp - _make_timestamp (2010;01;18))*24*3600+
extract(hour;current_timestamp - _make_timestamp (2010;01;18))*3600+
extract(minute;current_timestamp - _make_timestamp (2010;01;18))*60+
extract(second;current_timestamp - _make_timestamp (2010;01;18)))/86400;4)
This was posted as a mere example, but perhaps you did not make the effort to check whether it suits you. Worked pretty good with me..