CODEWITHSUNDEEP
sqloracle-databasetimestamporacle-apex
Julio
Julio

Reputation: 551

problem to convert timestamp format to char

having this query:

CREATE TABLE my_table 
   (    
    "CREATED" TIMESTAMP (6) DEFAULT sysdate NOT NULL ENABLE, 
    "CREATED_BY" VARCHAR2(256 BYTE) NOT NULL ENABLE, 
    "EFFECTIVEDATE" TIMESTAMP (6)
)

if I make a simple query to EFFECTIVEDATE column, look like this (after some insert) query:

select EFFECTIVEDATE
from my_table;

enter image description here

But if i want format that timestamp ... query:

select to_char(mt.EFFECTIVEDATE, 'YYYY-MM-DD HH:MM:SS') as "EffectiveDate"
from my_table mt

enter image description here

why it add those values into HH:MM:SS ?

What should i do to fix this? i need those values as a STRING

It must look like this:

2020-06-26 00:00:00
2020-06-24 00:00:00
2017-01-01 00:00:00
2017-01-01 00:00:00
2017-01-01 00:00:00

I'm using oracle 19.

Upvotes: 0

Views: 169

Answers (1)

Popeye
Popeye

Reputation: 35930

Because you are using MM instead of MI.

  • MM - Month
  • MI - Minute

You should use: to_char(mt.EFFECTIVEDATE, 'YYYY-MM-DD HH:MI:SS')

HH represents in 12 hour and HH24 represents 24 hour format

Upvotes: 4

Related Questions