Improve company productivity with a Business Account.Sign Up

x
?
Solved

Display result of DateDiff function as hh:mm:ss

Posted on 2004-08-18
15
Medium Priority
?
1,025 Views
Last Modified: 2008-03-17
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
Comment
Question by:tomfolinsbee
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 17

Expert Comment

by:BillAn1
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

by:Anthony Perkins
ID: 11832187
CONVERT(char(8), EndTime - StartTime, 108)
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11832464
acperkins, I though along those lines, but what if the time difference is > 24 hours?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:tomfolinsbee
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

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

Assisted Solution

by:AustinSeven
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

by:Anthony Perkins
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

by:tomfolinsbee
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
                           ORDER BY UserName DESC

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

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

by:tomfolinsbee
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

by:Anthony Perkins
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),
                  UserName,
                  DayCreated,
                  StartTime,
                  RecordCount,
                  EndTime,
                  DATEDIFF(n, StartTime, EndTime) AS MinutesWorked
FROM            dbo.Payroll
ORDER BY UserName DESC

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

by:Anthony Perkins
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

by:tomfolinsbee
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]
GROUP BY DayCreated, UserID, UserName
ORDER BY UserName, DayCreated DESC


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,
                      dbo.DocTypes.Name AS DocTypes, CONVERT(varchar(10), dbo.Insiders.DateCreated, 111) AS DayCreated, dbo.Docs.DownloadTime,
                      dbo.Docs.AssignedTime, dbo.Insiders.DateCreated AS CreatedTime, DATEDIFF(n, dbo.Docs.DownloadTime, dbo.Docs.AssignedTime) AS AssignLag,
                      DATEDIFF(n, dbo.Docs.AssignedTime, dbo.Insiders.DateCreated) AS ProcessLag, DATEDIFF(n, dbo.Docs.DownloadTime, dbo.Insiders.DateCreated)
                      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

Thanks for your patience!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
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

by:tomfolinsbee
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question