I have two fields in a data table - "startTime" and "endTime." These two fields represent a duration of time the user spent on a particular task. These are varchar fields. So, let's say we have a startTime of "21:05:00" and an endTime of "22:09:00." I need the code to sum the total number of minutes spent in hour 21, and the 22 hundred hour separately (i.e. 9 minutes). So, not just a simple minute difference, but a breakdown by hour.
What might be the best way to do that?
Thus far, I have created a table that will return all possible hours in a 24-hour period. Here's a sample:
Hour startTime endTime
0 2015-01-01 00:00:00.000 2015-01-01 01:00:00.000
1 2015-01-01 01:00:00.000 2015-01-01 02:00:00.000
2 2015-01-01 02:00:00.000 2015-01-01 03:00:00.000
And I have converted the startTime field from varchar to dateteime and called it sessionHour:
Convert(datetime, startTime) As sessionHour
Additionally, I was able to get the hour of the startTime by doing:
DateAdd(Minute, 60 * (DateDiff(Minute, 0, startTime) / 60), 0) As hourOf
Beyond that, I am lost as to how to parse out the minutes per hour. Help very much appreciated. Thanks!
Aucun commentaire:
Enregistrer un commentaire