Hi,

I am using Oracle 10.2.0.

I have a problem of calculating the difference between two date columns, which have the data like
column1 :
"6/25/2008 4:22:44 AM"

column2 :
"6/24/2008 12:44:22 PM"

For a report generation i need to find the difference between those two columns seperately for
1.No of Days
2.Time

I have done it for days like


But i have problems doing it for time column in reports ..

I tried like this


But i got error message

ORA-01849: HOUR must be BETWEEN 1 AND 12

please Advice...

TIA,

Hi
You have a number of issues with your dates.

First:
===
You say that your example data contains strings like '6/25/2008 4:22:44 AM' and '6/24/2008 12:44:22 PM' which use the AM / PM format for the time yet you have used a 12 hour (HH12) designation which is invalid. The correct formula and format mask for your dates are:

TO_DATE(Column1,'MM/DD/YYYY HH MI:SS AM') and TO_DATE(Column2,'MM/DD/YYYY HH MI:SS AM')

Second:
=====
Because column1 and column2 contains dates as well as time, you cannot just do this: TO_DATE(column1,'HH12:MI:SS')

If you do this: TO_DATE(Column1,'MM/DD/YYYY HH MI:SS AM') - TO_DATE(Column2,'MM/DD/YYYY HH MI:SS AM') you will end up with the NUMBER 0.65 where 0 represents the number of whole days between the two dates and 65 represents the percentage of a day.


The following logic explains in detail how to manipulate the difference between two dates:

Create a new calculation, called Diff, which is the basic calculation from above:
TO_DATE(Column1,'MM/DD/YYYY HH MI:SS AM') - TO_DATE(Column2,'MM/DD/YYYY HH MI:SS AM')

Then create the following calculations, finally only displaying the one called Result

Days = TRUNC(Diff)
Full Diff = TRUNC(Diff)||':'||TO_CHAR(SYSDATE+(Diff),'HH:MI:SS')


TO_CHAR(TRUNC( Diff ))||':'||TO_CHAR(TRUNC(SYSDATE)+( Diff ),'HH24:MI:SS')


Hours = TRUNC(MOD(Diff,TRUNC(Diff))*24)
Minutes = TRUNC(Diff*1440)-( Days*1440 )-( Hours*60 )
Seconds = TRUNC(Diff*86400)-( Days*86400 )-( Hours*3600 )-( Minutes*60 )

Result = Days||' Days, '||Hours||' Hours, '||Minutes||' Minutes, '||Seconds || ' Seconds'


Here it is as one algorithm:

Result: TRUNC(Diff) ||' Days, '|| TRUNC(MOD(Diff,TRUNC(Diff))*24) ||' Hours, '|| TRUNC(Diff*1440)-(TRUNC(Diff)*1440 )-(TRUNC(MOD(Diff,TRUNC(Diff))*24)*60 ) ||' Minutes, '|| TRUNC(Diff*86400)-(TRUNC(Diff)*86400 )-(TRUNC(MOD(Diff,TRUNC(Diff))*24)*3600 )-( Minutes*60 ) || ' Seconds'

If you want this as one big formula, you will have to substitute TO_DATE(Column1,'MM/DD/YYYY HH MI:SS AM') - TO_DATE(Column2,'MM/DD/YYYY HH MI:SS AM') for Diff

Best wishes
Michael

Hi Michael,

Thanks for your reply..

But when i use column name its giving error

This one is k

SELECT TO_DATE('5/30/2007 6:33:27 AM','MM/DD/YYYY HH:MI:SS AM') -
TO_DATE('5/30/2007 3:33:27 AM','MM/DD/YYYY HH:MI:SS AM') as diff
FROM dual;

DIFF
-------
0.125

This one gives error

SELECT TO_DATE(column1,'MM/DD/YYYY HH:MI:SS AM')
FROM
table_name

ORA-01843: NOT a valid MONTH

And also i need to find the hour difference between a single day. If that column contains the same date value.

please Advice...


TIA,

First, display the value and see why it's not a valid month value:

SELECT to_char( TO_DATE(column1,'MM/DD/YYYY HH:MI:SS AM'))

Hi
As already commented by someone else it sounds like you have an invalid month. The code I gave you will work so long as you have good data. I can't give workarounds for every possibly month issue. You're going to need to do some leg work and find out why for yourself. If this is a corporate system and you have found issues then you will have to get the data fixed.

As to your second issue, over dates within the same day, why would my answer not work?

Best wishes
Michael