Oracle: how to substract a date from table column from a fixed date

Question

I have a table containing a column in date format named "date_started". I want to substract all the dates from this column from a fixed date, for example 31.03.2022, resulting in a new column "absolut days" showing difference between two dates in days.

I tried with the following statement:

SELECT ('31.03.2022'- date_started) AS absolut days FROM ....

Unfortunately i am not able to find a workaround for the resulting Error message:

ORA-00932: Inkonsistente Datentypen: CHAR erwartet, DATE erhalten 00932. 00000 - "inconsistent datatypes: expected %s got %s"

I am beginner in SQL, exspecially in Oracle and looking forward for some help, thx!

Answer 1

'31.03.2022' may look like a date but it is not a DATE data type; it is a string literal.

If you want a DATE data type then you can use a date literal:

SELECT DATE '2022-03-31' - date_started AS absolut_days
FROM   your_table;

Or convert your string to a date:

SELECT TO_DATE('31.03.2022', 'DD.MM.YYYY') - date_started AS absolut_days
FROM   your_table;

Then, for the sample data:

CREATE TABLE your_table (date_started) AS
SELECT DATE '2022-01-01' FROM DUAL;

Both output:

ABSOLUT_DAYS
89

db<>fiddle here