how to find the difference between two date time fields in minutes and hours

Question

how to find the difference between two date time fields in minutes and hours

kish1234

Ten Centuries

Points: 1112
More actions
April 11, 2014 at 4:42 pm

#285686

Hi Team,
I have couple of datetime fields in a table called "Appointment". They are Appstart and AppEnd.
I want to find the difference between these two fields in MINS and in HOURS as separate columns.
How can achieve this?
regards,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Use the function DATEDIFF ( datepart , startdate , enddate ) it is in BOL (Books On Line) if you need to see examples of its use

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

klini SSC-Addicted Points: 458 More actions · Answer 2

I'm sure there's other ways, but here's what first came to mind:

create table #Appointment (AppStartDate datetime, AppEndDate datetime)

insert into #Appointment values('1/1/2014 8:30am', '1/1/2014 9:45 am')

select

*,

floor((CONVERT(float, AppEndDate) - CONVERT(float, AppStartDate)) * 24) hrs,

((CONVERT(float, AppEndDate) - CONVERT(float, AppStartDate)) * 24 - floor((CONVERT(float, AppEndDate) - CONVERT(float, AppStartDate)) * 24)) * 60 mins

from #Appointment

..or use DATEDIFF as bitbucket suggested 😉

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Becareful using DATEDIFF for the calculation of durations. The difference between 11:59PM and 12:00AM according to DATEDIFF is an hour because DATEDIFF doesn't calculate durations... it simply calculates the number of boundaries crossed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Try this:

create table #Appointment (AppStartDate datetime, AppEndDate datetime);

insert into #Appointment values('1/1/2014 8:30am', '1/1/2014 9:45 am');

select

*,

datediff(minute,AppStartDate,AppEndDate) / 60 hrs,

datediff(minute,AppStartDate,AppEndDate) % 60 mins

from #Appointment;

drop table #Appointment;