Subtract two datetime values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subtract two datetime values

Author

Topic

dhanlak
Starting Member

5 Posts

Posted - 2012-01-28 : 10:06:17

Hi,

I am searching for a way to find the total no of hours:min between two dates.

My table design is as below:

EmployeeId | StartTime | EndTime | BreakPeriod |

Sample data:
100 | 2011-09-14 08:00:00.000 | 2011-09-14 15:30:00.000 | 30

I need to find the total no of hours:mm between these two datetime values. (excluding the breakperiod).

-----

I tried the following query. But it includes the BreakPeriod

select
case when ltrim(rtrim(cast(floor(datediff(n,StartTime,EndTime)/60) as char(3)))) = 0 then '00'
else ltrim(rtrim(cast(floor(datediff(n,StartTime,EndTime)/60) as char(3)))) end + ':' +
case when ltrim(rtrim(cast(datediff(n,StartTime,EndTime) % 60 as char(3)))) = 0 then '00'
else ltrim(rtrim(cast(datediff(n,StartTime,EndTime) % 60 as char(3)))) end As TotalHrs
from
tblEmployee
where id = 5

I want to find solution for both scenarios. That is when (i) the breakperiod is in minutes for eg: 100 minutes and also (ii) when the breakperiod is in hh:mm eg: 1:45

Can anyone please help me with this problem. This is very urgent.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-28 : 10:38:25

Use the subtraction/addition operators. For example:


SELECT CAST(EndTime-StartTime as TIME);

That assumes that the starttime and endtime are less than 24 hours apart.

To subtract breakperiod, use either dateadd if it is a number or subtraction if it is in datetime format. For example:

SELECT CAST(DATEADD(mi,-BreakPeriod,@EndTime-@StartTime) as TIME);

If it is in time format, cast that to datetime and use the subtraction operator.

If you have a mix of time and integer minutes in the BreakPeriod column, that is not a great design. Keep it either as time or as integers, or keep them in two separate columns with appropriate data types. But if you must mix them, check if it can be parsed to an integer and then use integer subtraction or time subtraction as appropriate.

Edit: In the above, if you want to get the hours/minutes, use the datepart function. For example in the first query that I posted, to get the hours:

SELECT DATEPART(hour,CAST(EndTime-StartTime as TIME))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-28 : 11:34:09

SELECT DATEDIFF(MINUTE, StartTime, EndTime) - BreakPeriod

N 56°04'39.26"
E 12°55'05.63"

dhanlak
Starting Member

5 Posts

Posted - 2012-01-30 : 01:01:31

Hi all,
Thanks for your help. It was exactly what i was searching for. I realize that subtracting the break period from both starttime and endtime is the right thing to do.

I solved by using this following query. Will this query cause any problems?

SELECT
convert(varchar(5),convert(datetime,cast(((DATEDIFF(minute, StartTime, EndTime) - cast(BreakPeriod as int)) / 60) as char(5)) + ':' + cast(((DATEDIFF(minute, StartTime, EndTime) - cast(BreakPeriod as int)) % 60) as char(5))),108) as TotalCalculatedHours
from tblEmployee

Thank you so much again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-30 : 02:54:13

SELECT CONVERT(CHAR(5), DATEADD(MINUTE, DATEDIFF(MINUTE, StartTime, EndTime) - BreakPeriod, '19000101'), 8) AS TotalCalculatedHours

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources