Skip to Main Content
  • Questions

  • How to convert a number column to time

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, racman.

Asked: August 17, 2017 - 6:24 pm UTC

Last updated: August 19, 2017 - 1:51 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hello,
I have a number column in a table named TXNTIME which the data_type is a number:

I want to select the output of this column from a number to a time(HH24:MI:SS).

TXNTIME
-------
164741
164742
93935
172350
73016
174742
174743
.
.
.

For example, I want the output shown as:

TXNTIME
-------
16:47:41
16:47:42
9:39:35
17:23:50
7:30:16
17:47:42
17:47:43
.
.
.

I tried: select to_char(txntime, 'HH24:MI:SS') from webtxn;

And get

ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
*Action: Consult your manual.

I was wondering if there is an easy way to do this.

Thanks!

and Connor said...

Man, storing times as 24hr numbers is a bad idea. But anyway, the first issue is ambiguity, ie

10101

Is that 10am or 1am ?...So for the sake of this discussion we'll assume the last 4 digits are *always* mins and secs.

Now you didnt specify if you want the output to be a date or a string, so we'll look at each:

We'll start with a number, then convert to a string, and then either use date or string functions depending on what you're after:

SQL> variable x number
SQL> exec :x := 123456

PL/SQL procedure successfully completed.

SQL> select to_char(:x) from dual;

TO_CHAR(:X)
----------------------------------------
123456

1 row selected.

SQL> select substr(to_char(:x),-4) from dual;

SUBSTR(TO_CHAR(:
----------------
3456

1 row selected.

SQL> select substr(to_char(:x),1,length(to_char(:x))-4) from dual;

SUBSTR(TO_CHAR(:X),1,LENGTH(TO_CHAR(:X))-4)
------------------------------------------------------------------------------------
12

1 row selected.

SQL> select to_date(substr(to_char(:x),1,length(to_char(:x))-4)||':'||substr(to_char(:x),-4),'HH24:MISS') from dual;

TO_DATE(SUBSTR(TO_C
-------------------
01/08/2017 12:34:56


SQL> select lpad(substr(to_char(:x),1,length(to_char(:x))-4),2,'0')||':'||
  2         substr(to_char(:x),-4,2)||':'||
  3         substr(to_char(:x),-2)
  4  from dual;

LPAD(SUBSTR(TO_CHAR(:X),1,
--------------------------
12:34:56




Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

cast to date

Christian, August 18, 2017 - 6:55 am UTC

If your stored Timestamp is indeed always in the format hh24miss (without the dots), you could cast it to a date: 

$[CHE_TEST@glados_impl] select to_date(164741, 'hh24miss') d from dual;

D
-------------------
01.08.2017 16:47:41


(note that the date will always be the current day, but as you only care for the time this doesn't matter anyway)

And for display purposes cast it back into a varchar2 with the date format you'd like to see; e.g.

$[CHE_TEST@glados_impl] select to_char(to_date(164741, 'hh24miss'),'hh24:mi:ss') d from dual;

D
--------
16:47:41

$[CHE_TEST@glados_impl] select to_char(to_date(164741, 'hh24miss'),'hh24:mi') d from dual;

D
-----
16:47

$[CHE_TEST@glados_impl] select to_char(to_date(164741, 'hh24miss'),'hh:mi') d from dual;

D
-----
04:47


(or use the session nls_date_format parameter; but after that all dates will be displayed using this format)

cheers

Variant

Racer I., August 18, 2017 - 11:50 am UTC

Hi,

TO_DATE(LPAD(TO_CHAR(txntime), 6, '0'), 'HH24MISS')
Sergiusz Wolicki
August 18, 2017 - 2:33 pm UTC

Yet another method is to use an NLS trick for NUMBER formatting:

SELECT TO_CHAR(txmtime, 'fm00G00G00',
               'NLS_NUMERIC_CHARACTERS=''.:''')
  FROM webtxn



Thanks!

racman, August 18, 2017 - 3:45 pm UTC

Thank you everyone. Racer I., that is exactly what I was looking for. Just what exactly does this mean/do 'fm00G00G00'?

Thanks!
Connor McDonald
August 19, 2017 - 1:51 am UTC

I'll quote Sergiusz on this one:

Here, we use ':' as a numeric group separator. Groups do not need to be three digit long, like in English. In fact, there are languages where groups are variable length. 'Fm' is to trim the leading space (=positive sign).
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.