Find elapsed time in hours 2

[shannanl]

I have a table with two fields:

In_date
Out_date

I would like to select those two field and then calculate the elapsed time between Out_date and In_date in hours. How can I do this? Something like this:

In_date Out_date Hours
1:00:00 AM 1/20/2006 1:00:00 PM 1/20/2006 12
1:00:00 AM 1/20/2006 1:00:00 AM 1/21/2006 24
8:00:00 AM 1/20/2006 9:00:00 PM 1/20/2006 1

Thanks,

Shannan

 

[jbenson001]

In books on line, look up the DateDiff function

 

[jbenson001]

DATEDIFF ( datepart , startdate , enddate )

So try,
Select DATEDIFF(hh, In_date, Out_date )
From your table

 

[shannanl]

The datediff works good except for one thing. I need it to ALWAYS round up to the next day even if it is only 1 hour. DateDiff(D, 6/1/2006 10:20:00 AM, 6/1/2006 10:30:00 AM) will return zero. I need it to return one.

Thanks,

Shannan

 

[jbenson001]

you said you wanted hours not days

 

[shannanl]

That is true I did say hours. Mistake on my part.

 

[gmmastros]

Try...

Select Ceiling(1.0 * DateDiff(second, '6/1/2006 10:20:00 AM', '6/1/2006 10:30:00 AM') / 86400)


The ceiling function rounds up for you. You need to get the difference in a unit that is smaller than what you are tesing for. In this case, I used seconds (to get the number of seconds that have elapsed). This returns an integer. I multiple by 1.0 to convert it to real, then divide by 86400 (the number of seconds in a day).

Alternatively, you could do this...

Select Ceiling(Convert(Decimal(18,10), Convert(DateTime, '6/1/2006 10:30:00 AM') - Convert(DateTime, '6/1/2006 10:20:00 AM')))

Note that if you are working with field that are already datetime, then you don't need to do the conversion, so this should also work.

Select Ceiling(Convert(Decimal(18,10), Field1 - Field2)

This last trick only works for getting the days elapsed, and wouldn't work for hours, minutes or seconds (without some modification).

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

So what do you want to show then?
If datediff is different at all but < 24hours then show 1.
etc. ??

 

[shannanl]

Thank you guys very much. The CEILING function worked very well.

Thanks again,

Shannan

 

[gmmastros]

I'm glad to have helped.

There's also a Floor function that rounds down to the nearest integer (just in case you ever need that).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[shannanl]

Yes, I was not familar with the CEILING function so I pulled out my SQL book and read about the CEILING and FLOOR functions this weekend.

Thanks,

Shannan