Timestamp Conversion in Oracle for Yyyy-Mm-Dd Hh:Mm:Ss Format

Timestamp conversion in Oracle for YYYY-MM-DD HH:MM:SS format

INSERT INTO AM_PROGRAM_TUNING_EVENT_TMP1 
VALUES(TO_DATE('2012-03-28 11:10:00','yyyy/mm/dd hh24:mi:ss'));

http://www.sqlfiddle.com/#!4/22115/1

how to truncate yyyy/mm/dd hh:mm:ss.SSS to mm/dd/yyyy in oracle

The problem is in the conversion of to_date itself. The below conversion itself is throwing the error you mentioned

select 
TO_DATE('2017/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.SSS') END_DATE
from dual;

You need to use it like below if you want to convert the string with timestamp to timestamp

select TO_TIMESTAMP('2017/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.FF') from dual

This will simply satisfy your need rather than making so many conversions.

select to_char(
   add_months(
     TO_TIMESTAMP('2017/01/01 00:00:00.0', 'YYYY/MM/DDHH24:MI:SS.FF'),
   -2),
'mm/dd/yyyy') from dual

Issues handling YYYY-MM-DDTHH:MM:SS date format

You can use your date mask like this:

SELECT CODE_TCT, LIB_TCT, 
To_char(D_FIN,'yyyy-MM-dd"T"HH24:MI:SS') AS D_FIN,
FROM MY_TABLE;

How do I insert date in YYYY-MM -DD HH24:MI:SS in Oracle

INSERT INTO TABLE (COL1, ...) VALUES (
    to_date('2001-12-30 17:27:59', 'YYYY-MM-DD HH24:MI:SS')
); 

You submit a string and the date format to TO_DATE, and then use that in your VALUES clause of a TABLE INSERT command.

Oracle stores it as a DATE, there is no stored format of the date. When you query it out, you have the ability to get it back in the format of your choice, or leave it to the database or session parameters to be your default date format.

I want to Get Date in yyyy-mm-dd HH:mm:ss format

in my table local_datatime store in 24-DEC-14 format

No. A DATE is not stored in the format you see the way it is displayed. It is stored in 7 bytes which is Oracle's proprietary format.

Byte    Description

1       Century value but before storing it add 100 to it
2       Year and 100 is added to it before storing
3       Month
4       Day of the month
5       Hours but add 1 before storing it
6       Minutes but add 1 before storing it
7       Seconds but add 1 before storing it

AND erd.local_datetime = 'TO_DATE('2014-12-24 13:31:16', 'YYYY-MM-DD HH:MI:SS')'

You use single-quotation marks around a string which is not the case here. Remove the single quotes. Also, the time element seems to be HH24 format which you have missed.

AND erd.local_datetime = TO_DATE('2014-12-24 13:31:16', 'YYYY-MM-DD HH24:MI:SS')

Use TO_DATE to convert a string into date, use TO_CHAR to display a date in your desired format.

What is oracle format for last .0 in TO_DATE ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.TZR')?

The .0 is fractional seconds, not a timezone offset; but the date data type doesn't allow either anyway.

If your string always has .0 then you could treat that as fixed characters to ignore it:

select TO_DATE ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS".0"') from DUAL;

TO_DATE('2020-04-01
-------------------
2020-04-01 00:00:00

If it might be non-zero or just if you prefer it can convert to a timestamp and then cast that to a date:

select CAST( TO_TIMESTAMP ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') AS DATE) from DUAL;

CAST(TO_TIMESTAMP('
-------------------
2020-04-01 00:00:00

The FF format model element represents fractional seconds, and as the documentation says, that is "Valid in timestamp and interval formats, but not in DATE formats".

It isn't entirely cleat what getting the value from Java means, but if you're making a JDBC call and passing the parameter as a string you should step back and use the proper JDBC data type instead. Also note @Ole's comment about using a modern Java datatype if you can.

---

Related Topics