0

I have a column name 'Cos_Date' with value like 14APR2017:00:00:00.

However, for a new column name 'Arrival_Date', I would like to keep the date information but omit time, and keep the data type as Date but not Character. Ex, 14APR2017.

I have tried:

  select TO_CHAR(Cos_Date, 'DD-MON-YYYY') ARRIVAL_DATE

But it will delete time information, but data type turns to Character.

I search on this site, and tried both:

  select TO_DATE(TO_CHAR(Cos_Date, 'DD-MON-YYYY'), 'DD-MON-YYYY')  ARRIVAL_DATE

and:

  select TRUNC(Cos_Date) ARRIVAL_DATE

But it will not omit time information.

Can I try something else?

Thank you!

Chenxi
  • 297
  • 1
  • 4
  • 15
  • In Oracle there is no "pure" date data type. The date data type ALWAYS contains a time component. The best you can do is to have dates where the time component is 00:00:00. Why does that bother you? There is no problem created by this "limitation" of Oracle. (There **are** problems created by the lack of a "pure" **time** data type, with no date attached to it, but that's unrelated to your question.) –  Apr 17 '17 at 22:59
  • Oh, I see. Since I am using SAS to call Oracle and will let this table join another SAS table which contains data type is 'Date'. This bothers me. Maybe I can do one more data / proc sql step. Thanks @mathguy – Chenxi Apr 17 '17 at 23:06
  • I don't know how SAS works with Oracle, but if it works at all, and if SAS has "pure" date data type, I assume it is converted to date with the time set to 00:00:00 in Oracle. If you have two SAS tables with such date columns, you can simply join them in Oracle, the time portion being 00:00:00 on all dates will cause no problems in your join. –  Apr 17 '17 at 23:10
  • @mathguy Right, I will take a look and that's doable. – Chenxi Apr 17 '17 at 23:38

3 Answers3

1

You can't "omit" the time portion of a DATE column in Oracle. The DATE data type always contains a time component. If you don't want to see the time, don't display it, e.g.,

SELECT TO_CHAR(TRUNC(Cos_Date),'DD-MON-YYYY') FROM dual;
DCookie
  • 42,630
  • 11
  • 83
  • 92
0

In Oracle there is no date data type that has only a year-month-day component.

The DATE data type is stored internally as 7- or 8-bytes which always has year (2-bytes), month (1-byte), day (1-byte), hour (1-byte), minute (1-byte) and second (1-byte).

The TIMESTAMP data type also has fractional seconds (and can also have a time zone).

Can I try something else?

No, you either use a VARCHAR2 string or use a DATE or TIMESTAMP and accept that it has a time component.

Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117
0

Selecting date values without time:

SELECT date_col
  FROM table
 WHERE TO_CHAR (date_col, 'HH24:MI:SS') = '00:00:00';
Ralph Santiago
  • 21
  • 3