How to change date format '17.DEC.80' to '17.12.80' in Run SQL Command Line SCOTT Database?

Viewed 24

How to change date format '17.DEC.80' to '17.12.80' in Run SQL Command Line SCOTT Database?

1 Answers

It depends on what 17.DEC.80 actually is; if it is a string, then to_date it first (using appropriate format model), and then apply to_char with the final format:

SQL> select to_char(to_date('17.DEC.80', 'dd.mon-yy'), 'dd.mm.yy') like_this from dual;

LIKE_THI
--------
17.12.80

SQL>

If it is a date datatype value, then just to_char it, e.g.

SQL> create table test (col date);

Table created.

SQL> insert into test values (date '1980-12-17');

1 row created.

SQL> select col from test;

COL
---------
17-DEC-80

SQL> select to_char(col, 'dd.mm.yy') like_this from test;

LIKE_THI
--------
17.12.80

SQL>

Or, alter session to set format model:

SQL> alter session set nls_date_format = 'dd.mm.yy';

Session altered.

SQL> select * from test;

COL
--------
17.12.80

SQL>

Though: we've had Y2K issue 20 years ago and learnt that 2-digits years should be avoided. I suggest you do that as well and use 4 digits for years.

Related