Solved

Display result of DateDiff function as hh:mm:ss

Posted on 2004-08-18
15
984 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
 

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 100 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 150 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 250 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now