Convert the value to hh:mm:ss format

Question

Convert the value to hh:mm:ss format

pwalter83

SSChampion

Points: 14575
More actions
February 15, 2012 at 2:54 am

#391564

Hi,
I have a requirement where the numeric value which is returned as total hours should be displayed the format of hh:mm:ss.
The value returned through this calculation returns the value in hours and in whole number:
7 * count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end).
For e.g. when it returns the value 147, it should be displayed as 147:00:00.
Is it possible to achieve this ?
Thanks,
Paul

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Can you post all the DDL and sample data you have up to now so that we can create a test to help you out, tables, queries so far etc.

Also take a look at the convert function using the time data type and format code 108

Thanks

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 2

Hi Paul ,

You need to display the result 147 as 147:00:00 or 2:45:00

pwalter83 SSChampion Points: 14575 More actions · Answer 3

guruprasad1987 (2/15/2012)
Hi Paul ,
You need to display the result 147 as 147:00:00 or 2:45:00

Thanks for your reply. I want it to display as 147:00:00...as the value is returned in hours.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Formatting is something that's better done in the presentation layer. However, if you insist on doing it in the data layer, just convert the number to varchar(4) and concatenate ":00:00" on the end. If that doesn't work for you, please provide sample data as requested earlier by Anthony.

John