x
Solved

# Display result of DateDiff function as hh:mm:ss

Posted on 2004-08-18
Medium Priority
1,025 Views
I have a SQL Server 2000 database.

tblLog
StartTime (datetime,8)
EndTime (datetime,8)

qryShift
StartTime
EndTime
DateDiff(hh,StartTime,EndTime)

This query returns an integer for DateDiff.

I will be using this field in a production report, so how can I format it so that it dispalys hh:mm:ss?
I will also be using this field for payroll calculations.  Do I need a different approach, eg, display with decimals?

Thanks!

Tom
0
Question by:tomfolinsbee
• 6
• 5
• 3
• +1

LVL 17

Expert Comment

ID: 11832185
try this :

convert(varchar(3),DateDiff(hh,StartTime,EndTime)) + ':' +
right('0' + convert(varchar(2),DateDiff(mi,StartTime,EndTime)) ,2) + ':' +
right('0' + convert(varchar(2),DateDiff(ss,StartTime,EndTime)) ,2) + ':' +
0

LVL 75

Expert Comment

ID: 11832187
CONVERT(char(8), EndTime - StartTime, 108)
0

LVL 17

Expert Comment

ID: 11832464
acperkins, I though along those lines, but what if the time difference is > 24 hours?
0

Author Comment

ID: 11832555
StartTime = 18-08-2004 1:42:57 AM
EndTime = 18-08-2004 8:54:30 AM

BillAn1, the function you suggested returns this: 7:0*:0*: +.
I see now that I just need to delete the last bit.

convert(varchar(3),DateDiff(hh,StartTime,EndTime)) + ':' +
right('0' + convert(varchar(2),DateDiff(mi,StartTime,EndTime)) ,2) + ':' +
right('0' + convert(varchar(2),DateDiff(ss,StartTime,EndTime)) ,2)

But I think this solution will only work if the number of hours or minutes is <10.

acperkins, I tried your function but I get "data type error in expression" when I try to enter it into the query designer. Any idea why?

What I'm thinking of doing now is use DateDiff(mm, StartTime,EndTime) as MinutesWorked and then calculate in a second query  HoursWorked = MinutesWorked/60. It won't look quite right, but I will be able to use it in the payroll calculation.

Any other suggestions?

0

Author Comment

ID: 11832620
Actually, just tried HoursWorked=MinutesWorked/60 and I still get an integer.... back to square 1!
0

LVL 10

Assisted Solution

AustinSeven earned 400 total points
ID: 11832622
As for your other caluations, you would use the datetime values directly and just convert the output to something else.  eg. minutes worked...

declare @startTime  datetime
declare @endTime    datetime
Declare @hardTime int
select @starttime = '09:00:00'
select @endtime = '19:15:00'
select @hardTime = datediff(minute, @starttime, @endtime)

-- then you can use ordinary arithmatic functions/operators to work out the other stuff.
eg. Stupid report example...
select 'Time Worked: ' + cast(@hardTime/60 as varchar(2)) + ' hours, ' +  cast((@hardTime % 60) as varchar (2)) + ' Minutes'

AustinSeven
0

LVL 75

Expert Comment

ID: 11832739
>>
StartTime = 18-08-2004 1:42:57 AM
EndTime = 18-08-2004 8:54:30 AM
..
acperkins, I tried your function but I get "data type error in expression" when I try to enter it into the query designer. Any idea why?
<<
Yes. StartTime and EndTime are not dates.  Try it like this:

Declare @StartTime datetime, @EndTime datetime
SET @StartTime = '2004-08-18 1:42:57 AM'
SET @EndTime = '2004-08-18 8:54:30 AM'

Select CONVERT(char(8), @EndTime - @StartTime, 108)

I get:
07:11:33
0

Author Comment

ID: 11833044
acperkins,
I'm actually confused in the first place that StartTime and EndTime are not DateTime format... I checked the table in design view and confirmed that, in the table at least, they are indeed DateTime.

I've never had to use the DECLARE or SET contructs. Seems like I can't use it in the query designer. I tried it anyway, and received a message "1 row affected by query".

Here is the complete query I used

DECLARE @StartTime datetime, @EndTime datetime
SET              @StartTime = '2004-08-18 1:42:57 AM'
SET              @EndTime = '2004-08-18 8:54:30 AM'
SELECT     TOP 10 CONVERT(char(8), @EndTime - @StartTime, 108), UserName, DayCreated, StartTime, RecordCount, EndTime, DATEDIFF(n,
StartTime, EndTime) AS MinutesWorked
FROM         dbo.Payroll

I definitely don't want to be modifying records in my database - I just want to use a select query.

0

LVL 17

Accepted Solution

BillAn1 earned 600 total points
ID: 11833120
sorry, I forgot the crucial %

that said, acperkin's is the right way to go if you never go over 24 hrs.

Declare @StartTime datetime, @EndTime datetime

SET @StartTime = '2004-08-18 1:42:57 AM'
SET @EndTime = '2004-08-19 8:54:30 AM'

Select convert(varchar(3),DateDiff(ss,@StartTime,@EndTime) / (60*60)) + ':' +
right('0' + convert(varchar(2),(DateDiff(ss,@StartTime,@EndTime)/60) % 60 ) ,2) + ':' +
right('0' + convert(varchar(2),DateDiff(ss,@StartTime,@EndTime) % 60 ) ,2)
0

Author Comment

ID: 11833835
Will using Declare or Set change any rows in my table?  Still have no idea why I got that message about one row being modified.

I'm not writing any code here, I'm just desigining a query for a report I'm designing using SQL Server Reporting Services.
0

LVL 75

Assisted Solution

Anthony Perkins earned 1000 total points
ID: 11833942
>>I'm actually confused in the first place that StartTime and EndTime are not DateTime format... <<
Sorry, my comment was somewhat misleading to say the least.  I should have said the values:
18-08-2004 1:42:57 AM
18-08-2004 8:54:30 AM

would be best (unambiguosuly) written as:
'2004-08-18 1:42:57 AM'
'2004-08-18 8:54:30 AM'

The question you need to ask is did the code I posted give you the right result.  Here it is repeated:

Declare @StartTime datetime, @EndTime datetime
SET @StartTime = '2004-08-18 1:42:57 AM'
SET @EndTime = '2004-08-18 8:54:30 AM'

Select CONVERT(char(8), @EndTime - @StartTime, 108)

Than you have to understand that was just an example.

Next, the query you posted, namely:
DECLARE @StartTime datetime, @EndTime datetime
SET @StartTime = '2004-08-18 1:42:57 AM'
SET @EndTime = '2004-08-18 8:54:30 AM'
SELECT      TOP 10
CONVERT(char(8), @EndTime - @StartTime, 108),
DayCreated,
StartTime,
RecordCount,
EndTime,
DATEDIFF(n, StartTime, EndTime) AS MinutesWorked
FROM            dbo.Payroll

When executed can never produce:
1 row affected by query.
It is a SELECT statement.  No one is "modifying records" or are they suggesting you do so.

In order to give you the complete solution, you need to tell us:
1. As BillAn1 has pointed out: Can the time elapsed go beyond 24 hours.

2. Where does StartTime and EndTime columns come from?  In other words:  What table?  (Your original question had them in tblLog and in your latest query it is in the Payroll table)

3. If more than one table is involved the relationship between them.
0

LVL 75

Expert Comment

ID: 11833961
>>Will using Declare or Set change any rows in my table?  <<
No.
DECLARE:  Dims the variable
SET: Assigns a value to that variable.
0

Author Comment

ID: 11834278
I just ran it again: "1 row affected by query". I clicked help and get this "A number of rows were affected.
The query executed successfully. The specified rows were affected by a make table process".

The time elapsed time will never go beyond 24 hours.

StartTime and EndTime come from a query called "Payroll". Here is the Payroll query:

SELECT     TOP 100 PERCENT UserName, DayCreated, MIN(CreatedTime) AS StartTime, COUNT(InsiderID) AS RecordCount, MAX(CreatedTime) AS EndTime
FROM         dbo.[QC Production]

This is the query for QC Production:
SELECT     TOP 100 PERCENT dbo.Insiders.ID AS InsiderID, dbo.Docs.ID AS DocID, dbo.Users.ID AS UserID, dbo.Users.Name AS UserName,
AS TotalLag
FROM         dbo.DocTypes INNER JOIN
dbo.Docs ON dbo.DocTypes.ID = dbo.Docs.DocTypeID RIGHT OUTER JOIN
dbo.Users RIGHT OUTER JOIN
dbo.Insiders ON dbo.Users.ID = dbo.Insiders.CreatorID ON dbo.Docs.ID = dbo.Insiders.DocID
WHERE     (dbo.Users.Status = 'Active')
ORDER BY dbo.Insiders.ID DESC

and Insiders.DateCreated has Data Type = datetime, 8

0

LVL 75

Expert Comment

ID: 11834366
Where are you executing this query?  If you are not doing it in SQL Query Analyzer, please do it there.
0

Author Comment

ID: 11834546
I just created a new view then ran it.

I just tried SQL Query Analyzer, and I no longer get that '1 row affected' message. Thanks.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.