Input Time format change

[nnani]

Hello all,

I am trying to convert a integer into time data type

Code: Select all

nnani=> select cast(cast(162545 as varchar) as time);
 ?column?
----------
 16:25:45
(1 row)

This is working fine

But when I try

Code: Select all

nnani=> select cast(cast(62545 as varchar) as time);
ERROR 3679:  Invalid input syntax for time: "62545"
nnani=>

It is obvious that Vertica is expecting one more digit for converting the int to Time, but can we somehow give the input as 062545 so that Vertica will give 06:25:45 as the time

I have got this data from flat file and this is the standard it come in.
How do I convert this input "62545" into time.

Thanks

[JimKnicely]

I think you gotta get a leading zero in your string:

Code: Select all

dbadmin=> select cast(to_char(62545, '099999') as time);
 to_char
----------
 06:25:45
(1 row)

dbadmin=> select cast(to_char(162545, '099999') as time);
 to_char
----------
 16:25:45
(1 row)

[nnani]

Thanks knicely,

That worked like a charm