getting datetime difference..

[pooja]

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

[MFGF]

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.

[pooja]

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

[pooja]

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

[MFGF]

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.

[pooja]

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...

[MFGF]

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.

then 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.

[pooja]

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....

[pooja]

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

[blom0344]

As posted in an earlier post on datetime differences:

Code Select Expand


_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..