sql calculating minutes for two dates between two times

Question

I have a table with two DateTimes (StartDateTime, EndDateTime) and I want to calculate the minutes between the two.

Now, I know to use the DATEDIFF() function to get this, but I only want to get the number of minutes that fall between two TIME(0) values.

For example:

  StartDateTime     | EndDateTime         | TimeStart | TimeEnd
2017-06-01 03:00:00 | 2017-06-01 23:00:00 | 06:00:00  | 20:00:00

For the above example, I would get: DATEDIFF(minute, StartDateTime, EndDateTime) = 1200.

However, I want to only get the number of minutes that fall between TimeStart and TimeEnd, which would be 840.

How can I write my query to get this?

Tag your question with the database you are using. – Gordon Linoff Jun 04 '17 at 15:38 — Gordon Linoff, Jun 04 '17 at 15:38

score 8 · Answer 1 · answered Jun 04 '17 at 15:40

8

In SQL Server, DATEDIFF() works on time values, so you can do:

DATEDIFF(minute, TimeStart, TimeEnd)

You can run this as a demonstration:

select DATEDIFF(minute, cast('06:00:00' as time), cast('20:00:00' as time))

answered Jun 04 '17 at 15:40

Gordon Linoff

1 Answers1