-2

I have a column like this 120081212. the format of the column is Number.

I used:

SELECT to_date(to_char(SUBSTRING(My_Column,2,6)) ,'yymmdd')
FROM My_Table

The output is: 2008-12-12 00:00:00.000. But I want just 2008-12-12.

The format of the output is not important.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user16810982
  • 7
  • 1
  • Documentation for reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm. Also, a [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) would be very helpful. – WOUNDEDStevenJones Jan 06 '22 at 16:48

1 Answers1

2

In Oracle, a DATE is a binary data-type that consists of 7 bytes for century, year-of-century, month, day, hour, minute and second and it ALWAYS has those components. It NEVER stores any formatting information.

Therefore, if you want a DATE to have a particular format then you need to convert it to another data-type (i.e. a string) that can store the formatted value:

SELECT my_column,
       TO_CHAR(
         TO_DATE(
           SUBSTR(My_Column,2,8),
           'yyyymmdd'
         ),
         'YYYY-MM-DD'
       ) AS formatted_value
FROM   My_Table;

(Note: you want SUBSTR rather than SUBSTRING and you want to take 8 characters and not 6.)

Which, for the sample data:

CREATE TABLE My_Table (My_Column) AS
SELECT '120081212' FROM DUAL;

Outputs:

MY_COLUMN FORMATTED_VALUE
120081212 2008-12-12

If you do want it as a DATE then remove the TO_CHAR:

SELECT my_column,
       TO_DATE(
         SUBSTR(My_Column,2,8),
         'yyyymmdd'
       ) AS date_value
FROM   My_Table;

However, you will then be relying on whatever user interface you are using to format the date to a human readable format. In SQL/Plus and SQL Developer, this is based on the NLS_DATE_FORMAT session parameter and the default format is dependent on your territory.

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117