asked on

SQL Case Statement - Time Format issue

I have the following issue with some data in a table
TRANS_TIME = HHMMSS (Example 10:00AM = 100000)

We have the command below to convert the time because we have sometimes in TRANS_TIME a value of 09:00AM, which is stored as 90000. So we use the statement below :-

CASE WHEN TRANS_TIME >= 100000 THEN CAST(TRANS_TIME AS char(6)) ELSE '0'+ CAST(KAS_ZEIT AS char(5)) END

Problem is we have TRAN_TIME values of 300 for (00:03:00) for example causing issue and wanted to to know the best way to deal will all interpretations of TRANS_TIME up until the last second (00:00:01) which is stored in TRANS_TIME as 1.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Deepak Chauhan

Try this , I think this is what you are looking for.

select  STUFF(STUFF(REPLICATE('0',6-LEN(TRANS_TIME)) + 
convert(VARCHAR(6),TRANS_TIME),3,0,':'),6,0,':') as TRANS_TIMES

From Table

Open in new window

Peter Chan

Try

-- Convert one number into one time
declare
  @int int,
  @str varchar(10),
  @str2 varchar(10),
  @len int,
  @tm datetime
  
set @str='000000'
set @int=300
set @str2=rtrim(ltrim(cast(@int as varchar)))
set @len=len(@str2)
set @str2=substring(@str,1,6-@len)+@str2
set @str2=substring(@str2,1,2)+':'+substring(@str2,3,2)+':'+substring(@str2,5,2)
select CONVERT(datetime,@str2,114)

Open in new window

Vitor Montalvão

No need for CASE. Try this:

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

Open in new window