Click here to Skip to main content
14,638,900 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

How can I compute hours work from time in: '7:59' to time out: '18:03' subtract 1hour
I've test sql datediff but it returns 10. I want result be like 9.4

can anyone help?

thanks
Posted
Comments
DamithSL 1-Jul-14 23:20pm
   
update the question with the sql statement you tried
syed shanu 2-Jul-14 0:01am
   
Chk this :
DECLARE @START_DATE DATETIME



DECLARE @END_DATE DATETIME -- Set Start and End date
SET @START_DATE ='2014-07-02 07:59'
SET @END_DATE = '2014-07-02 18:03'


-- Get the Result in HH:MI:SS:MMM(24H) format
SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff
Sanjay K. Gupta 2-Jul-14 0:25am
   
You can post this as a solution not as a comment.
Cuculala 2-Jul-14 1:27am
   
Thanks a lot it works great. that's what i've looking for
Rate this:
Please Sign up or sign in to vote.

Solution 1

Try this query. Good luck.

select (datediff(MINUTE, convert(datetime,'7:59'),convert(datetime,'18:03'))/60)-1
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Chk this :
DECLARE @START_DATE DATETIME 

 
DECLARE @END_DATE DATETIME -- Set Start and End date
SET @START_DATE ='2014-07-02 07:59'
SET @END_DATE = '2014-07-02 18:03'

-- Get the Result in HH:MI:SS:MMM(24H) format
SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

DECLARE @START_DATE DATETIME



DECLARE @END_DATE DATETIME -- Set Start and End date
SET @START_DATE ='2014-07-02 07:59'
SET @END_DATE = '2014-07-02 18:03'


-- Get the Result in HH:MI:SS:MMM(24H) format
SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff


--code credited by: syed shanu
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100