14,638,900 members
Rate this:
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
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:

## Solution 1

Try this query. Good luck.

select (datediff(MINUTE, convert(datetime,'7:59'),convert(datetime,'18:03'))/60)-1
Rate this:

## 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:

## 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