Display numeric as time

Assuming you are using Oracle SQL for the smart note:

to_char(to_date(to_char(your_timestamp, '0000'),'HH24MI'),'HH24:MI')

This converts the numeric to a four digit right zero padded string and then converts that back to a datetime value. It then reconverts the datetime back into a character string in 24 hour format with a : between the hour and the minutes.

I'm making assumptions that your SQL environment doesn't have a built in time only datatype (for instance Oracle has a timestamp datatype, however it is actually a datetime value with added precision on the seconds) if you did, you might be able to cut out the outer to_char and let SQL format the output on it's own.

But lacking that, if you attempted to run the above without manually specifying the format to use for the output, you'd get something more along the lines of '01-May-15' without any reference to a time.

Thank you for your reply. I've inserted this formula into the statement. I've tried the following iterations:
TO_CHAR(to_date(TO_CHAR(ZZTMEDTTRN.IN_PUNCH, '0000'), 'HH24MI'), 'HH24:MI') -Error: ORA-01722: invalid number
TO_CHAR(to_date(TO_CHAR(.IN_PUNCH, '0000'), 'HH24MI'), 'HH24:MI') -Error: ORA-01722: invalid number
to_date(TO_CHAR(ZZTMEDTTRN.IN_PUNCH, '0000'), 'HH24MI'), 'HH24:MI') -Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Not sure where to go with these errors, any thoughts are welcome.

Another thought....If this is a numeric field as my table suggests, what about dividing by 100 and then replacing the decimal with a colon?
Again, not sure how to construct that, but it was another thought I had. I could do those things in Access or Crystal, but I'm not as versed in SQL Developer.

Unless that third version is missing text, your value doesn't seem to be the numeric you thought it was.

It may already be a datetime or it could be a character string already.

The first error, the only function that is expecting a number is the innermost TO_CHAR. And it's complaining that your timestamp isn't an number. The second is just a repeat. The third however is complaining that you are passing it a datetime when it wants a number. to_date doesn't want numbers, it wants dates. So the only thing that could be complaining there is to_char.

Try just
TO_CHAR(zztmedttrn.in_punch,'HH24:MI') - assuming it's a date
or
to_char(to_date(lpad(your_timestamp,4, '0'),'HH24MI'),'HH24:MI') - assuming it's text.

or alternatively, since you mention you have SQL Developer installed pull up the table and on the columns tab, what is the datatype the field is defined as?

Hi All,

I was able to get a code that works. I am sharing it below:
to_char("ZZTMEDTTRN"."IN_PUNCH",'fm00G00','nls_numeric_characters=,:') as "IN_PUNCH",
The code above changes 1030 to 10:30 and 430 to 04:30.
Thank you to all who helped!