Skip to Main Content
  • Questions

  • How to convert only HH24:MI:SS from SYSDATE?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Choi.

Asked: September 29, 2016 - 6:48 am UTC

Last updated: November 28, 2022 - 6:04 am UTC

Version: Oracle SQL Developer 4.1.3.20

Viewed 10K+ times! This question is

You Asked

Hello.

I have a quick question about Oracle 11g SQL command.

I need to get only HH24:MI:SS data from SYSDATE;

for example, select TO_DATE(SYSDATE, 'HH24:MI:SS') from dual;

I tried it then error is like below.

ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.

appreciate any ideas. thanks in advance.

and Connor said...

SQL> select to_char(sysdate,'HH24:MI:SS') from dual;

TO_CHAR(
--------
20:49:22


But note - this is a *string* that has come back, not a date.

Rating

  (3 ratings)

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

Comments

A reader, September 29, 2016 - 10:41 pm UTC


TIME AND DATE - SYSDATE WITH TIME

AHSAN, November 25, 2022 - 12:20 pm UTC

HOW TO - TIME AND DATE WITH SYSDATE AND SYSTIME
EXAMPLE :
I HAVE SAVE THE TIME AND DATE IN FIELD THEN HOW TO - WITH SYSTEM TIME AND DATE AND CHECK DURATION

Connor McDonald
November 28, 2022 - 6:04 am UTC 

SQL> with t as
  2  ( select
  3       date '2021-01-01' dte,
  4       '04:15:00' tim
  5    from dual
  6  )
  7  select
  8    to_date(
  9       to_char(dte,'DD-MM-YYYY')||tim,
 10       'DD-MM-YYYYHH24:MI:SS')
 11  from t;

TO_DATE(TO_CHAR(DTE
-------------------
01/01/2021 04:15:00

BERFE, November 27, 2023 - 11:04 am UTC

You can do;

SELECT TO_CHAR(SYSDATE, 'HH.DD.YYY')
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.