Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1022
  • Last Modified:

Display result of DateDiff function as hh:mm:ss

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
tomfolinsbee
Asked:
tomfolinsbee
  • 6
  • 5
  • 3
  • +1
3 Solutions
 
BillAn1Commented:
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
 
Anthony PerkinsCommented:
CONVERT(char(8), EndTime - StartTime, 108)
0
 
BillAn1Commented:
acperkins, I though along those lines, but what if the time difference is > 24 hours?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tomfolinsbeeAuthor Commented:
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
 
tomfolinsbeeAuthor Commented:
Actually, just tried HoursWorked=MinutesWorked/60 and I still get an integer.... back to square 1!
0
 
AustinSevenCommented:
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
 
Anthony PerkinsCommented:
>>
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
 
tomfolinsbeeAuthor Commented:
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
 
BillAn1Commented:
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
 
tomfolinsbeeAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
>>Will using Declare or Set change any rows in my table?  <<
No.  
DECLARE:  Dims the variable
SET: Assigns a value to that variable.
0
 
tomfolinsbeeAuthor Commented:
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
 
Anthony PerkinsCommented:
Where are you executing this query?  If you are not doing it in SQL Query Analyzer, please do it there.
0
 
tomfolinsbeeAuthor Commented:
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.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now