Oracle: How to convert the following date to a different format:

in my select query i have the following

substr(to_date(NEXT_ARRIVAL_DATE, 'yyyy-mm-dd'), 0, 10)

Which yields:

08-JUN-11

What i need is it to yield:

2011-06-08

EDIT: The data was coming in wrong. sorry. The below workds fine

to_char(to_date(NEXT_ARRIVA开发者_运维百科L_DATE, 'mm/dd/yyyy'), 'YYYY-MM-DD') ||  ' 00:000:00'

---

Your DATE_NEXT_ARRIVAL column obviously has a date datatype.

SQL> create table t23 (next_arrival_date date)
  2  /

Table created.

SQL> insert into t23 values (sysdate+7)
  2  /

1 row created.

SQL> select to_date(next_arrival_date, 'YYYY-MM-DD')
  2  from t23
  3  /

TO_DATE(N
---------
11-JUN-08

SQL>

If you want to display the date in a different format you need to use TO_CHAR() i.e. convert it to a string:

SQL> select to_char(next_arrival_date, 'YYYY-MM-DD')
  2  from t23
  3  /

TO_CHAR(NE
----------
2011-06-08

SQL>

If you have to do this for a whole bunch of dates, you might want to change the session settings instead....

SQL> alter session set nls_date_format='YYYY-MM-DD'
  2  /

Session altered.

SQL> select sysdate, next_arrival_date
  2  from t23
  3  /

SYSDATE    NEXT_ARRIV
---------- ----------
2011-06-01 2011-06-08

SQL>

---

in Oracle you can convert a DATE column to string with

to_char(NEXT_ARRIVAL_DATE, 'yyyy-mm-dd')

but it looks like the value of NEXT_ARRIVAL_DATE is a string in the required format. so you can just do select NEXT_ARRIVAL_DATE from ...