Right pad trailing zero

Database
Enthusiast

Right pad trailing zero

I need to get hour and minutes into this format: 99:99.  

 

I'm trying to report 1/2 hour interval data for a location that is 1 or 2 hours behind our server time depending on DST.  The date field I have to use is a bigint (20161228) and I can't convert it to a timestamp so I can't use 'at local'.  

So I'm going around it by using the interval hours (subtracting an hour) and minutes.  These are integers.  Then I concatenate them but the top of the hour drops a zero.  

It looks like this:  9:0

This is what I'm doing in my select statement:

 

vl.hours - 1 as newhr, --(results are like this: 9 or 12)
cast(cast(vl.minutes as format '9(2)') as char(2)) as minutes,  --(results are 00 or 30)
trim(newhr) || ':' || trim(minutes) as tmzone, --(results like this: 9:0 or 12:30)
tmzone || SUBSTRING('*****',1,5-LENGTH('0')) ,  --(I found this and tried it but get an error: expected something between a string or a Unicode character literal and ',')
vl.avaya_interval (format '99:99'),  --(results like this: 10:00)

 

I also tried this: CAST(CAST(vl.avaya_interval AS INTERVAL HOUR(4)) AS INTERVAL HOUR(4) )- interval '0100' hour , --(results like this:  700.  but it doesn't work very well in my SSRS report - I get #error or 16.16:00:00 no matter how I format it).

 

Thanks for helping.


Accepted Solutions
Enthusiast

Re: Right pad trailing zero

I should have walked away for a while and come back to it.  I searched using different keywords and found a solution I could use.  This was in a post from a couple of years ago.  

 

(Yr (FORMAT '9(4)') (CHAR(4)))||'-'|| (Mth (FORMAT '9(2)') (CHAR(2)))||'-'|| (Dy (FORMAT '9(2)') (CHAR(2))) (DATE) AS Dt

 

Modified to (vl.hours - 1 (FORMAT '9(2)') (CHAR(2)))||':'|| (vl.minutes (FORMAT '9(2)') (CHAR(2)))  

1 ACCEPTED SOLUTION
1 REPLY
Enthusiast

Re: Right pad trailing zero

I should have walked away for a while and come back to it.  I searched using different keywords and found a solution I could use.  This was in a post from a couple of years ago.  

 

(Yr (FORMAT '9(4)') (CHAR(4)))||'-'|| (Mth (FORMAT '9(2)') (CHAR(2)))||'-'|| (Dy (FORMAT '9(2)') (CHAR(2))) (DATE) AS Dt

 

Modified to (vl.hours - 1 (FORMAT '9(2)') (CHAR(2)))||':'|| (vl.minutes (FORMAT '9(2)') (CHAR(2)))