Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert a string date into datetime in Oracle

How can I convert this string date to datetime in oracle.

2011-07-28T23:54:14Z

Using this code throws an error:

TO_DATE('2011-07-28T23:54:14Z',  'YYYY-MM-DD HH24:MI:SS')

How can this be done?

Error report:
SQL Error: 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.

Update:-

TO_DATE('2011-07-28T23:54:14Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

I only see the date not time in the column

28-JUL-11
like image 409
arsenal Avatar asked Oct 27 '11 18:10

arsenal


People also ask

How do I convert a string to a date?

Using strptime() , date and time in string format can be converted to datetime type. The first parameter is the string and the second is the date time format specifier. One advantage of converting to date format is one can select the month or date or time individually.

How do I convert a string to a date in SQL Developer?

To convert a string to date, you use the Oracle date format elements e.g., YYYY for the 4-digit year, MM for the 2-digit month, etc. If you fail to construct the format, the TO_DATE() function will issue a respective error. The following statement inserts a new row into the members table.

How do I change the date format in Oracle?

where the <format> string has the same options as in TO_CHAR. Finally, you can change the default DATE format of Oracle from "DD-MON-YY" to something you like by issuing the following command in sqlplus: alter session set NLS_DATE_FORMAT='<my_format>'; The change is only valid for the current sqlplus session.

Why we use TO_DATE function in Oracle?

The purpose of the TO_DATE function in Oracle is to convert a character value to a date value. In reality, it converts any value which has a data type of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 into a value which has the data type of DATE. It doesn't convert the value into any of the other datetime datatypes.


3 Answers

Try this: TO_DATE('2011-07-28T23:54:14Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

like image 70
Forgotten Semicolon Avatar answered Oct 21 '22 20:10

Forgotten Semicolon


Hey I had the same problem. I tried to convert '2017-02-20 12:15:32' varchar to a date with TO_DATE('2017-02-20 12:15:32','YYYY-MM-DD HH24:MI:SS') and all I received was 2017-02-20 the time disappeared

My solution was to use TO_TIMESTAMP('2017-02-20 12:15:32','YYYY-MM-DD HH24:MI:SS') now the time doesn't disappear.

like image 22
Sebastian Biner Avatar answered Oct 21 '22 22:10

Sebastian Biner


You can use a cast to char to see the date results

 select to_char(to_date('17-MAR-17 06.04.54','dd-MON-yy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') from dual;
like image 26
DenisJC Avatar answered Oct 21 '22 20:10

DenisJC