convert an interger to time

Question

Post reply

convert an interger to time

atwilke

SSC Rookie

Points: 45
More actions
May 19, 2010 at 9:35 am

#235197

hello all,
Can someone provide me the sql to convert 015136 to 1:51:36 (h:m:s)
Thanks

Viewing 9 posts - 1 through 9 (of 9 total)

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

atwilke SSC Rookie Points: 45 More actions · Answer 1

the field to convert is called audit_time. the output format is 015136. it should be 01:51:36

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi there, try this:

select stuff( stuff(cast(015136 as varchar),2,0,':') , 5,0,':')

😎

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

I will go further and give you this:

1. First the sample table and sample rows set up

DECLARE @NUM_TABLE TABLE

(

NUM INT

)

INSERT INTO @NUM_TABLE

SELECT 11536 UNION

SELECT 123057 UNION

SELECT 11015 UNION

SELECT 24657 UNION

SELECT 54587

2. Now the code:

SELECT

CASE LEN(CAST(NUM AS VARCHAR))

WHEN 5 THEN '0'+STUFF( STUFF(CAST(NUM AS VARCHAR),2,0,':') , 5,0,':')

WHEN 6 THEN STUFF( STUFF(CAST(NUM AS VARCHAR),3,0,':') , 6,0,':')

END [TIME]

FROM

@NUM_TABLE

Hope this helps you! Tell us if this worked for you!

😎

atwilke SSC Rookie Points: 45 More actions · Answer 4

I am really new at this and could not get it to work. this is what I entered and keep receiving error msg

select audit_time ( audit_time(cast(audit_time as varchar),2,0,':') , 5,0,':')

atwilke SSC Rookie Points: 45 More actions · Answer 5

I'm working in crystal reports. the tables are already set up. Thanks.

WayneS SSC Guru Points: 95461 More actions · Answer 6

Slight modifications to the solution already provided by ColdCoffee, in order to show with the field name you specified. And a way to eliminate the case statement.

DECLARE @YOUR_TABLE TABLE

(

audit_time INT -- <<< LOOK! your field name is being used!

)

INSERT INTO @YOUR_TABLE

SELECT 11536 UNION ALL

SELECT 123057 UNION ALL

SELECT 11015 UNION ALL

SELECT 24657 UNION ALL

SELECT 54587 UNION ALL

SELECT 1 UNION ALL -- <<<< LOOK!!! really small numbers work also!

SELECT 100

-- Don't assume any length: always pad out to 6 with leading zeros.

-- Then insert the column at the 5th and then the 3rd positions.

-- By working from the back of the string forward, we don't have to

-- deal with the string changing lengths after each STUFF call.

SELECT STUFF(STUFF(RIGHT('000000' + CAST(audit_time AS VARCHAR),6),5,0,':'),3,0,':')

FROM @YOUR_TABLE

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

WayneS (5/19/2010)
Slight modifications to the solution already provided by ColdCoffee, in order to show with the field name you specified. And a way to eliminate the case statement.
DECLARE @YOUR_TABLE TABLE
(
audit_time INT -- <<< LOOK! your field name is being used!
)
INSERT INTO @YOUR_TABLE
SELECT 11536 UNION ALL
SELECT 123057 UNION ALL
SELECT 11015 UNION ALL
SELECT 24657 UNION ALL
SELECT 54587 UNION ALL
SELECT 1 UNION ALL -- <<<< LOOK!!! really small numbers work also!
SELECT 100
-- Don't assume any length: always pad out to 6 with leading zeros.
-- Then insert the column at the 5th and then the 3rd positions.
-- By working from the back of the string forward, we don't have to
-- deal with the string changing lengths after each STUFF call.
SELECT STUFF(STUFF(RIGHT('000000' + CAST(audit_time AS VARCHAR),6),5,0,':'),3,0,':')
FROM @YOUR_TABLE

Awesome wayne, Thanks for the rectified code mate 🙂

atwilke SSC Rookie Points: 45 More actions · Answer 8

thanks everyone! got it to work....

local stringvar audit_time;

audit_time := trim({AUDIT_TRAIL.AUDIT_TIME});

left(audit_time,2) & ":" & mid(audit_time, 3,2) & ":" & right(audit_time, 2)