srinu nivas srinu nivas -4 years ago 158
SQL Question

how to get accurate date and time of timestamp column in oracle 11g?

i am executing below two select statements.but it will shown difference.

1.

select TO_CHAR(AUTHORIZE_DATE,'YYYY-MM-DD') || 'T' ||
TO_CHAR(AUTHORIZE_DATE,'HH24:MM:SS')
from table_name


output is:

2017-02-21T05:02:58


2.

select AUTHORIZE_DATE
from table_name


output is:

2/21/2017 5:35:58


required output:

2017-02-21T05:35:58


but i am not able to getting accurate time.please help on this issue?Here column data type is Timestamp.

Answer Source

The right way to get that format is this (all with one TO_CHAR(), just use all its features):

select to_char(sysdate, 'yyyy-mm-dd"T"hh24:mi:ss') as now from dual;

NOW
-------------------
2017-03-01T22:49:56
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download