2

I have a TIMESTAMP(6) column filled with data. Time information is not important anymore. How can I migrate this to the DATE column and save data?

What is the easiest way to do this in SQL?

Aswin A
  • 104
  • 10
Mike
  • 812
  • 9
  • 25
  • 1
    Are you aware that a `DATE` column **still** has a time part? –  Nov 18 '19 at 12:46
  • @a_horse_with_no_name how comes? – Mike Nov 18 '19 at 13:03
  • 1
    @Mike In Oracle both [`DATE`](https://stackoverflow.com/a/13568348/1509264) and `TIMESTAMP` always have year, month, day, hour, minute and second components. `TIMESTAMP` may have optional fractional seconds and/or time zone components. There is no `DATETIME` data type in Oracle. Just converting to a `DATE` is not going to get rid of the time component. – MT0 Nov 18 '19 at 13:07
  • @Mike: because that's what Oracle decided over 40 years ago how a DATE column should look like –  Nov 18 '19 at 13:09
  • Looks like a `TIMESTAMP` internally takes 20 bytes and a `DATE` 8 bytes, so in theory you may save some space, but unless you have a lot of columns to change, you may not save much. Also be careful that the application does *not* need timezone information as the `DATE` datatype cannot store time zone information. – Mark Stewart Nov 18 '19 at 13:17

2 Answers2

2

A DATE column always has year, month, day, hour, minute and second components so converting to a DATE is not going to eliminate those time components (but will discard the fractional seconds stored in the TIMESTAMP).

Add the new column:

ALTER TABLE table_name ADD new_date_column DATE;

Then use an implicit cast:

UPDATE table_name
SET new_date_column = old_timestamp_column;

Or an explicit cast:

UPDATE table_name
SET new_date_column = CAST( old_timestamp_column AS DATE );

Then you can review the changes and drop the old column.

If you want to set all the time components to zero then you can use TRUNC:

UPDATE table_name
SET new_date_column = TRUNC( old_timestamp_column );

db<>fiddle

If you just want to change the column's data type without reviewing the data then use VBokšić's answer or modify the column and then use TRUNC to zero the time components:

ALTER TABLE table_name MODIFY column_name DATE;
UPDATE table_name SET column_name = TRUNC( column_name );
MT0
  • 143,790
  • 11
  • 59
  • 117
2

I believe you can do direct modify to this column and all will be ok:

ALTER TABLE testTable
MODIFY test_c date;

DEMO

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • 2
    @Andrew: the difference is, that this is more efficient as it doesn't create a new column as MT0's answer does. –  Nov 18 '19 at 12:48
  • 1
    @Andrew This answer modifies the column in place; so if the aim is just to cast to a simpler format discarding any fractional seconds then this will do it simply and efficiently. If there is a need to review the changes then you might need to create another column (i.e. if it was a `TIMESTAMP WITH TIME ZONE` column and you want to convert the data to a common time zone rather that just discard the fractional seconds and time zone) but that doesn't appear to be the case for the OP. – MT0 Nov 18 '19 at 12:50
  • I just wanted to note, if this is the case: "Time information is not important anymore" that this is a valid option then...And when I check the description about the down vote it says: "This answer is not useful". Well I do not see why it is not... – VBoka Nov 18 '19 at 12:50