SQL Server Performance

Converting a datetime to hh:mm:ss format

Discussion in 'SQL Server 2008 General DBA Questions' started by jace808, Apr 1, 2011.

  1. jace808 New Member

    I'm am tracking employees individual job times. I'm given there beginning time and their ending time in 2 columns both datetime format. I worked out an ugly DATEDIFF to give me individual components but it's not completely usable.
    I have been struggling with this issue of how to convert a datatime to hh:mm:ss. If the amount of time it took the employee was 1 hour, 8 minutes, and 9 seconds I don't want to see 1:8:9, I want to see 01:08:09.
    *Side note: My server is 2008 R2 and the server I'm querying is 2008 R1 which is causing "Invalid Column Name" errors, but our company has locked out being able to use the Microsoft Update page which is really annoying considering it's necessity in circumstances such as these where I need that update.
    TIA
  2. satya Moderator

    Welcome to the forums.
    Can you post the sampel TSQL that you are using to execute.
    Techinically as per your thread both SQL Server 2008 & 2008 R2 do not have major changes in representing the query values.
    Is this representation of time presented to the appilcation (front end) or storing in a database?
    Also see http://msdn.microsoft.com/en-us/library/ms187928.aspx
    fyi.
  3. jace808 New Member

    This is the uglier version. I'm currently updating with the hotfixes to my SQL Server or I would give you the current which still is not working.
    I'm not presenting a front-end at this time. I'm just gathering some raw data from our db.
    I apologize for the coloring I had to copy this out of an email. The yellow highlighted fields from the db and the green is what I'm getting "Invalid column names" errors with. Hopefully these updates will resolve that part at least.
    This is the format the last 3 "INDICATED" fields are in: 2011-02-16 11:31:26.000SELECT [IDDPORDERNUM] ,[ORDERTYPE] ,[EN_ROUTE_INDICATED] ,[ON_SITE_INDICATED] ,[ENDED_INDICATED] ,[WORKED_HOURS] = CASE WHEN LEN((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600))) = 1 THEN '0' + STR((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600))) + ':' WHEN LEN((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600))) = 2 THEN STR((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600))) + ':' END ,[WORKED_MINUTES] = CASE WHEN LEN((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60))- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600)*60) = 1 THEN '0' + STR(FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60)- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600)*60) + ':' WHEN LEN((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60))- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600)*60) = 2 THEN STR((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60)- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/3600)*60)) + ':' END ,[WORKED_SECONDS] = CASE WHEN LEN((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED]))- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60)*60)) = 1 THEN '0' + STR((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED]))- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60)*60)) WHEN LEN((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED]))- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60)*60)) = 2 THEN STR((FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED]))- FLOOR(DATEDIFF(ss,[ON_SITE_INDICATED],[ENDED_INDICATED])/60)*60)) END ,[WORKED_TIME] = [WORKED_HOURS] + [WORKED_MINUTES] + [WORKED_SECONDS]FROM [<catalog>].[dbo].[<table>]WHERE CONVERT(DATE,[ENDED_INDICATED] ,113) BETWEEN '2011-01-22' AND '2011-02-21'
  4. FrankKalis Moderator

    You might hide some of the ugliness by using a function called via CROSS APPLY. [:)]
    The following might work as well, but you will run into trouble when start and end time span more than one day as the third column demonstrates:
    SELECT
    DATEADD(SECOND, DATEDIFF(SECOND, GETDATE(), GETDATE() + 0.0833), 0),
    CONVERT(char(8), DATEADD(SECOND, DATEDIFF(SECOND, GETDATE(), GETDATE() + 0.0833), 0), 114), --looks good
    CONVERT(char(8), DATEADD(SECOND, DATEDIFF(SECOND, GETDATE(), GETDATE() + 1.0833), 0), 114) --not quite correct

    1900-01-01 01:59:57.000 01:59:57 01:59:57

    (1 row(s) affected)


  5. jace808 New Member

    <P mce_keep="true">[quote user="FrankKalis"] <P>You might hide some of the ugliness by using a function called via CROSS APPLY. <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"></P><P>[/quote]</P><P>&nbsp;When I was referring to the "ugliness" I meant how the layout&nbsp;pasted in, but I looked up CROSS APPLY, as you said it, and I definitely like that much more than using a join.&nbsp; Many thanks to you on mentioning that function!!</P>
  6. Flexdog New Member

    A shorter version (ran fine on sql2k8 R1 & R2):
    'worked_time'=convert(varchar(8),cast(datediff(ss,ON_SITE_INDICATED,ENDED_INDICATED)/86400.0 as datetime),14)
  7. Flexdog New Member

    lol - Frank is a faster typist than I. I might delete my posts...
  8. FrankKalis Moderator

    [quote user="Flexdog"]
    lol - Frank is a faster typist than I. I might delete my posts...
    [/quote]
    No, please don't do this! [:)]
  9. jace808 New Member

    Thanks guys! This has helped me out A LOT. :)
  10. jace808 New Member

    Well I found an issue that I didn't state would be needed previously.
    I'm also trying to total the time each employee spent on their individual jobs through the course of a month and of course the following gave me an error. How could I resolve this?
    I'm going through a different table now hence StartTime and StopTime replace ON_SITE_INDICATED and ENDED_INDICATED respectively. They both have the same datetime format however so this changes nothing.,
    SUM(CONVERT(varchar(8),CAST(DATEDIFF(ss,i.StartTime,i.StopTime)/86400.0 as datetime),14)) as Total_Worked_Time
  11. Flexdog New Member

    here is *hh:mm:ss (* for hrs beyond 99)
    Test code:
    DECLARE @s datetime='20110401', @e datetime='20110407 18:30:10'
    SELECT
    'total_worked_time'=
    CASE WHEN (CAST(@e as float)-CAST(@s as float))>1.0 THEN
    cast(floor((CAST(@e as float)-CAST(@s as float))*24.0) as varchar)+
    substring(convert(varchar(8),cast( ((cast(@e as float)-CAST(@s as float))-FLOOR((cast(@e as float)-CAST(@s as float)))) as datetime),14),3,6)
    ELSE
    convert(varchar(8),cast( ((cast(@e as float)-CAST(@s as float))-FLOOR((cast(@e as float)-CAST(@s as float)))) as datetime),14)
    END
    Your code (basically replace datediff - substraction with your columns and add sum for aggregate)

    SELECT
    'total_worked_time'=
    CASE WHEN sum(CAST(i.stoptime as float)-CAST(i.starttime as float))>1.0 THEN
    cast(floor(sum(CAST(i.stoptime as float)-CAST(i.starttime as float))*24.0) as varchar)+
    substring(convert(varchar(8),cast( (sum(cast(i.stoptime as float)-CAST(i.starttime as float))-FLOOR(sum(cast(i.stoptime as float)-CAST(i.starttime as float)))) as datetime),14),3,6)
    ELSE
    convert(varchar(8),cast( (sum(cast(i.stoptime as float)-CAST(i.starttime as float))-FLOOR(sum(cast(i.stoptime as float)-CAST(i.starttime as float)))) as datetime),14)
    END

    You could use patindex to elim case -> ;) it's your fun... now I've to get back to work & worry!
  12. jace808 New Member

    Wow... That totally went over my skill level. ;)
  13. satya Moderator

    Jace,
    As I see that expert input is required...hence pulled Frank into discussion. Glad to see your problem is resolved.
  14. jace808 New Member

    Yes and thank you. This is a great forum!
  15. FrankKalis Moderator

    Your solution seems to always "steal" one second for me. Took me some time to figure out why.
    DATEDIFF using seconds as interval cuts off the milliseconds part which may or may not lead to rounding issues. Here is another suggestion that requires even less keystrokes:
    DECLARE @StartTime datetime;
    DECLARE @EndTime datetime

    SELECT
    @StartTime = GETDATE(),
    @EndTime = DATEADD(HOUR, 1, @StartTime)

    SELECT
    CONVERT(varchar(8), CAST(DATEDIFF(ss, @StartTime, @EndTime)/86400.0 AS datetime), 14),
    CONVERT(varchar(8), DATEADD(ss, DATEDIFF(ss, @StartTime, @EndTime), 0), 14),
    CONVERT(varchar(8), DATEADD(ms, DATEDIFF(ms, @StartTime, @EndTime), 0), 14)

    00:59:59 01:00:00 01:00:00

    (1 row(s) affected)


Share This Page