Get Difference In Minutes between 2 dates

[dwayned]

Hi guys ,
I am creating a timekeeping application to keep track of users.

In my database I have a table which stores the time a user starts their break and finishes their break. I want to return the total time that the user is on break in minutes to another row called "totalbreak"

BreaksTable
=========
break_id - int
rec_id - int
userid - int
breakStart - time
breakEnd - time
breaktotal - int
reason - text

I have tried to calculate the difference in minutes between the two dates using ;

SELECT DATEDIFF(MI, breakstart,  breakend) from RP_RecordBreaks where break_id=2

However this returns a value of -1071.

If somebody could point me in the right direction, it would be greatly appreciated.

thanks very much
Dwayne

[MeSampath]

Hi,

Could you give a try with following way,

SELECT DATEDIFF(mi,convert(char(19),breakstart,121),convert(char(19),breakend,121)) AS 'Duration' FROM RP_RecordBreaks;

Even though date time is being stored in the db in AM/PM or 24H format, we will convert it into a standard format and find the difference.

121 - yyyy-mm-dd hh:mi:ss.mmm(24h)

for more formats have a look into msdn

Good luck.

[dwayned]

Hi MeSampath,

Sorry for only getting back to you now, I had came across the link below from another tutorial and it was quite handy.

It is now returning the correct value, code I used is below.

Thanks for your reply

Good luck.

--Calculate break total here
SET @b_start = (SELECT CONVERT (varchar, breakStart, 108) FROM RP_RecordBreaks WHERE break_id=@activebreakid)
SET @b_end = (SELECT CONVERT (varchar, breakEnd, 108) FROM RP_RecordBreaks WHERE break_id=@activebreakid)

SELECT DATEDIFF (N, @bs, @be)