Formating INT value to custom HH:MM:SS using expression for data labels

Question

Formating INT value to custom HH:MM:SS using expression for data labels

wwwnlc111

Old Hand

Points: 385
More actions
May 6, 2016 at 2:35 am

#316288

Hi,
I have INT value like 103743 or 33059, which going to aggregate in my chart for/by different dates.
I want to use expression for show data labels, and want to show like HH:MM:SS instead of 103743 or 33059
103743 i.e. 10 Hou, 37 Min and 43 Sec
33059 i.e. 3 Hou, 30 Min and 59 Sec
If you look carefully the length of INT value can we 5 or 6 digit, so if 6 digit then first 2 digit HH, next 2 digit MM and last 2 digit SS. But if length of digit is 5 then first digit HH, next 2 digit MM and last 2 digit SS.
Thanks.

Viewing 15 posts - 1 through 15 (of 22 total)

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

tripleAxe SSCertifiable Points: 5706 More actions · Answer 1

If you are doing it in T-SQL you could prefix with a leading 0 and then use the RIGHT function to get the right most 6 characters and then use the STUFF function to insert the : character in the relevant positions.

wwwnlc111 Old Hand Points: 385 More actions · Answer 2

Well I need to show the time format like HH:MM:SS, so if I am getting 33058 mean 3 Hou, 30 Min and 58 Sec.

and what you suggesting add 0, like 033058 , so it will look like 03 Hou, 30 Min and 58 Sec.

However can you please share expression for data labels..

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 3

DECLARE @i INT

Select @i=33059

Select CASE WHEN LEN (@i)=5 THEN '0'+LEFT(CAST(@i as VARCHAR(2)),1)+':'+SUBSTRING(CAST(@i as VARCHAR(2)),2,2)+':'+RIGHT(CAST(@i as VARCHAR(2)),2)

WHEN LEN (@i)=6 THEN LEFT(CAST(@i as VARCHAR(2)),2)+':'+SUBSTRING(CAST(@i as VARCHAR(2)),2,2)+':'+RIGHT(CAST(@i as VARCHAR(2)),2) END

OR

DECLARE @i INT,@var varchar(6)

Select @i=103743

SELECT @var=CAST(@i as VARCHAR(6))

Select CASE WHEN LEN (@i)=5 THEN '0'+LEFT(@var,1)+':'+SUBSTRING(@var,2,2)+':'+RIGHT(@var,2)

WHEN LEN (@i)=6 THEN LEFT(@var,2)+':'+SUBSTRING(@var,2,2)+':'+RIGHT(@var,2) END

wwwnlc111 Old Hand Points: 385 More actions · Answer 4

Hi Pulivarthi Sasidhar

I have the requirements such a way I have to carry INT from SQL end and have to do HH:MM:SS in SSRS Report Builder only for showing data labels, using expression.

So please suggest in expression for SSRS.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 5

If you also have a integer value for the date part then you could use the agent_datetime function to convert the int to a datetime in your dataset and then use the Format code HH:mm:ss in the SSRS report.

SELECT msdb.dbo.agent_datetime(20150119,173540)

wwwnlc111 Old Hand Points: 385 More actions · Answer 6

To aggregate we need INT or decimal data type,

Okay my requirement is to show in last 30 days specific SQL job what time executed.

Now here I have two output from SQL

1. Date

2. RUN_TIME

AS RUN_TIME coming INT so we can aggregate in any chart, but if we enable show data label it will show INT value, which I wish to HH:MM:SS using expression.

I hope you get my problem.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 7

A thousand apologies my friend, but I am not understanding what your problem is.

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 8

Do this expression at the backend Proc or SQL Query and show it as in String in front end..?

wwwnlc111 Old Hand Points: 385 More actions · Answer 9

This expression I have to do in SSRS Report Designer

Just you think with designer how we can show HH:MM:SS instead of INT

Thom A SSC Guru Points: 99133 More actions · Answer 10

If you're looking to do it in an SSRS expression, this will work (I assume you are not looking to convert it to a datetime value):

=left(right("0" + Parameters!TestParam.Value,6),2) + ":" + mid(right("0" + Parameters!TestParam.Value,6),3,2) + ":" + right(right("0" + Parameters!TestParam.Value,6),2)

Just replace the Parameters!TestParam.Value with the appropriate field/variable/parameter name.

if you want it converted into a datetime, a little different, but can be done. Just reply 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

wwwnlc111 Old Hand Points: 385 More actions · Answer 11

I applied as you suggested but it seems some more modification is require.

I am attaching screen shot for you for reference.

Default

after applying expression

I hope it will give you clear idea.

http://s32.This image host is not supported, please use another/oar349v7p/Default.png

http://s32.This image host is not supported, please use another/cnk06fozp/After_appllying_expression.png

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

Here's a formula to get it.

DECLARE @t TABLE( iTime INT);

INSERT INTO @t VALUES(33059),(103743);

SELECT iTime,

RIGHT(STUFF( STUFF( 1000000 + iTime, 6, 0, ':'), 4, 0, ':'), 8)

FROM @t;

Avoid using integer and character values for date/time values.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

wwwnlc111 Old Hand Points: 385 More actions · Answer 13

Hi,

I have INT value like 103743 or 33059, which going to aggregate in my chart for/by different dates.

I want to use expression for show data labels, and want to show like HH:MM:SS instead of 103743 or 33059

103743 i.e. 10 Hou, 37 Min and 43 Sec

33059 i.e. 3 Hou, 30 Min and 59 Sec

If you look carefully the length of INT value can we 5 or 6 digit, so if 6 digit then first 2 digit HH, next 2 digit MM and last 2 digit SS. But if length of digit is 5 then first digit HH, next 2 digit MM and last 2 digit SS.

Please note i have to do using expression only in ssrs report deigner.

Thanks.

Thom A SSC Guru Points: 99133 More actions · Answer 14

wwwnlc111 (5/7/2016)
Hi,
I have INT value like 103743 or 33059, which going to aggregate in my chart for/by different dates.
I want to use expression for show data labels, and want to show like HH:MM:SS instead of 103743 or 33059
103743 i.e. 10 Hou, 37 Min and 43 Sec
33059 i.e. 3 Hou, 30 Min and 59 Sec
If you look carefully the length of INT value can we 5 or 6 digit, so if 6 digit then first 2 digit HH, next 2 digit MM and last 2 digit SS. But if length of digit is 5 then first digit HH, next 2 digit MM and last 2 digit SS.
Please note i have to do using expression only in ssrs report deigner.
Thanks.

This is exactly what you posted in your original post. if we aren't giving you the answer you need, please help us my being more specific. Give examples your expected results or images of how you want it to display. Let us know where we're not meeting your requirements.

From what you're asking, i can see several valid answers. What is it we're missing?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk