How to change the string using convert in ms sql?

Question

I have this query that shows how many hours, minutes and seconds a worker has done for a day:

SELECT 
   Employee_Number, CAST([DateTime] as Date) as 'DateTime', 
   MIN([DateTime]) as 'MIN', MAX([DateTime]) as 'MAX', 
   [Hours Worked] = CAST((DATEDIFF(HOUR , min([DateTime]),  
   max([DateTime])) % 24 ) AS VARCHAR)  +':'+ CAST((DATEDIFF(MINUTE, min([DateTime]), 
   max([DateTime])) % 60) AS VARCHAR) + ':' + CAST((DATEDIFF(SECOND, min([DateTime]), 
   max([DateTime])) % 60) AS VARCHAR) + '0' from tblExtract group by Employee_Number, 
   Cast([DateTime] as Date)

My problem is I want to convert the Hours worked column in hh:mm:ss. How can I resolve it?

More information about your question please, what language are you using? why do you want your date in hours? if you explain better you will get more and better answers — Juanpe
– Juanpe, Commented May 29, 2014 at 6:52
@GaryPerry what I want is to convert the values of Hours Worked column to this format hh:mm:ss. I am using sql server 2012 — 20131620
– 20131620, Commented May 29, 2014 at 6:59

qxg · Accepted Answer · 2014-05-29 07:15:53Z

1

CONVERT(NVARCHAR, MAX([DateTime]) - MIN(DateTime]), 108) AS [Hours Works]

edited May 29, 2014 at 7:15

answered May 29, 2014 at 7:05

qxg

7,0961 gold badge31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ankit Bajpai · Accepted Answer · 2014-05-29 07:04:33Z

1

Try to use this at the palce of hours_worked:-

[Hours Worked] = CONVERT(VARCHAR(8),DateTime,108)

Hope this will be helpful to you.

answered May 29, 2014 at 7:04

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Comments

Nithesh Narayanan · Accepted Answer · 2014-05-29 07:26:29Z

1

Just use the Convert() function using the pattern for hh:mm:ss like so:

CONVERT(VARCHAR(8),DateTime,108)

Other patterns for convert are listed here.

edited May 29, 2014 at 7:26

Nithesh Narayanan

11.8k35 gold badges102 silver badges140 bronze badges

answered May 29, 2014 at 7:09

SQL_wiz

604 bronze badges

Comments

Tarun Suneja · Accepted Answer · 2014-05-29 07:34:40Z

1

use like this

[Hours Worked] = CONVERT(VARCHAR(15),CAST((DATEDIFF(HOUR , min([DateTime]),108)

or simply

[Hours Worked] = CONVERT(VARCHAR(15),min([DateTime]),108)

Here are the different formats

edited May 29, 2014 at 7:34

Tarun Suneja

33 bronze badges

answered May 29, 2014 at 7:02

Nithesh Narayanan

11.8k35 gold badges102 silver badges140 bronze badges

Collectives™ on Stack Overflow

How to change the string using convert in ms sql?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related