Time Calculations in hours and minutes for same day or different day

[trojnfn]

I have a start date/time of 4/13/09 10:08 and end date/time time of 4/13/09 11:55 and I need to calculate the time between both. It would be one hour and 47 minutes on the same day.

If I use datediff with hh, it returns 1 (one hour). If I use datediff with mm, it returns 0. What is the correct way/formula to calculate the time difference to come out with hours and minutes, and how do I add the am or pm ?

If the end date/time is 4/14/09 11:55, then that would be one day, one hour and 47 minutes, or 25 horus and 47 minutes ? How is that done if the date is the next day ?

[Dan_Bracuk]

Reading your post, I was not able to determine what you are trying to accomplish.  Having said that, it sounds like you want to get the datediff in minutes and do whatever math you need to do to get your hours.

Also, if these date objects are coming from a db, it would be easier to use your db's date functions instead of Cold Fusion's.

[trojnfn]

I am just trying to determine the time differenece between the start and end dates above, with the outcome in hours and minutes. I am on sql server and tried using sql datediff("hh",startdate,enddate) but that only returns the hour, using datediff("mm",startdate,enddate), returns 0, so how do I calculation to get the difference in hours and minutes.

[Dan_Bracuk]

First get it in minutes.  Then, divide by 60 and do an int function on the answer.  That will be the hours.  Then do a mod(60) on the answer.  That will be the minutes.

ps.  read your reference material again for dateparts in sql server.  Then you'll know why "mm" gives you 0.

[trojnfn]

I found some code from another post that does what you suggest.

However, I am not sure where to apply the formula. It cannot be inside the query so it has to outside using <cfset> to define the variables. But how do I apply it to each record to get the time difference ?

If my query returns ten records, how do I apply the fornula to each record to get the time difference and display it ?

[Dan_Bracuk]

mssql has an floor function you can use to get the hours.  To get the minutes, first select some number in your query.  Then loop through your results and modify that number with the modulus operator.