convert string literal to a date

I have a varchar2 field in my db with the format of for example -

2015-08-19 00:00:01.0
2014-01-11 00:00:01.0
etc.

I am trying to convert this to a date of format DD-MON-YYYY. For instance, 2015-08-19 00:00:01.0 should become 19-AUG-2015. I've tried

 select to_date(upgrade_date, 'YYYY-MM-DD HH24:MI:SS') from connection_report_update

but even at this point I'am getting ORA-01830 date format ends before converting the entire input string. Any ideas?

---

You have details upto milli seconds, for which, you have to use TO_TIMESTAMP() with format model ' FF '

select to_timestamp('2015-08-19 00:00:01.0' ,'YYYY-MM-DD HH24:MI:SS.FF') as result from dual;

RESULT
---------------------------------------------------------------------------
19-AUG-15 12.00.01.000000000 AM

And Date doesn't have a format itself, only the date output can be in a format. So, when you want it to be printed in a different format, you would need to again use a TO_CHAR() of the converted timestamp;

select to_char(to_timestamp('2015-08-19 00:00:01.0' ,'YYYY-MM-DD HH24:MI:SS.FF'),'DD-MON-YYYY') as result from dual;

RESULT
-----------
19-AUG-2015

---

Why do you store datetimes in a string???

Anyhow. To get from '2015-08-19 00:00:01.0' to a datetime with milliseconds (which is a TIMESTAMP in Oracle) use to_timestamp :

to_timestamp('2015-08-19 00:00:01.0', 'yyyy-mm-dd hh24:mi:ss.ff')

Then to get the desired output format, use to_char :

to_char(thedate, 'DD-MON-YYYY')

Together:

to_char(to_timestamp('2015-08-19 00:00:01.0', 'yyyy-mm-dd hh24:mi:ss.ff'), 'DD-MON-YYYY')

---

您应该在呼叫to_date时指定所需的格式，而不是当前的格式：

select to_date(upgrade_date, 'DD-MM-YYYY') from connection_report_update

链接地址: http://www.djcxy.com/p/36958.html

上一篇: 使用当前日期列值插入语句

下一篇: 将字符串文字转换为日期