January 28, 2012 at 8:03 am
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:
1 | 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.
January 28, 2012 at 8:58 am
something like this is what you are looking for, i think.
note how i created consumable data with the Common Table Expression and made sure to create the "right" data types?
if you can do that int he future, you'll get instantly testable solutions.
With mySampleData (EmployeeId,StartTime,EndTime,BreakPeriod)
AS
(
SELECT
convert(integer,1) ,
convert(datetime,'2011-09-14 08:00:00.000'),
convert(datetime,'2011-09-14 15:30:00.000'),
convert(integer,30)
)
SELECT DATEDIFF(minute,StartTime,EndTime) - BreakPeriod,
(DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60 as hours,
(DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60 as minutes
from mySampleData
Lowell
January 28, 2012 at 9:01 am
if you are SQL 2000 you can move the CTE to a subquery like this:
SELECT DATEDIFF(minute,StartTime,EndTime) - BreakPeriod,
(DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60 as hours,
(DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60 as minutes
from (
SELECT
convert(integer,1)AS EmployeeId ,
convert(datetime,'2011-09-14 08:00:00.000') AS StartTime,
convert(datetime,'2011-09-14 15:30:00.000') AS EndTime,
convert(integer,30 ) AS BreakPeriod
)mySampleData
Lowell
January 29, 2012 at 11:02 pm
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
January 30, 2012 at 6:27 am
dhanalakshmi 99938 (1/29/2012)
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
What was wrong with Lowell's code?
SELECT
CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60) AS VARCHAR(5)) + ':' +
CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60) AS VARCHAR(5))
FROM tblEmployee
Don't you think that's easier to read?
January 31, 2012 at 5:39 am
Because it displays values like this:
8:50
5:0
8:10
3:0
8:0
But this format is preferable,
8:50
5:00
8:10
3:00
8:00
January 31, 2012 at 7:50 am
You could always just add a little string manipulation and still keeping is a lot easier to read.
SELECT
CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60) AS VARCHAR(5)) + ':' +
LEFT(CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60) AS VARCHAR(5)) + '0', 2)
FROM tblEmployee
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2012 at 6:40 am
Sean Lange (1/31/2012)
You could always just add a little string manipulation and still keeping is a lot easier to read.
SELECT
CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60) AS VARCHAR(5)) + ':' +
LEFT(CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60) AS VARCHAR(5)) + '0', 2)
FROM tblEmployee
Funnily enough, I'd use RIGHT instead. Not sure why :w00t:
SELECT
CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60) AS VARCHAR(5)) + ':' +
RIGHT('0' + CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60) AS VARCHAR(5)),2)
FROM tblEmployee
February 1, 2012 at 8:21 am
Cadavre (2/1/2012)
Sean Lange (1/31/2012)
You could always just add a little string manipulation and still keeping is a lot easier to read.
SELECT
CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60) AS VARCHAR(5)) + ':' +
LEFT(CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60) AS VARCHAR(5)) + '0', 2)
FROM tblEmployee
Funnily enough, I'd use RIGHT instead. Not sure why :w00t:
SELECTCAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) / 60) AS VARCHAR(5)) + ':' +
RIGHT('0' + CAST(((DATEDIFF(minute,StartTime,EndTime) - BreakPeriod) % 60) AS VARCHAR(5)),2)
FROM tblEmployee
You take the RIGHT road and I'll take the LEFT road, and I'll be in Scotland before ye. :Whistling:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2012 at 10:06 am
Sean Lange (2/1/2012)
You take the RIGHT road and I'll take the LEFT road, and I'll be in Scotland before ye. :Whistling:
Oh dear. . . has it been a long day? 😛
February 1, 2012 at 10:25 am
Cadavre (2/1/2012)
Sean Lange (2/1/2012)
You take the RIGHT road and I'll take the LEFT road, and I'll be in Scotland before ye. :Whistling:Oh dear. . . has it been a long day? 😛
Sadly, that was at the beginning of my day. Given that my brain starts the day that far LEFT of center you can imagine where it is by the end of the day. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply