select column based on date interval converted from unix timestamp in Oracle sql

Question

I have to run a select from table based on a condition from a column with a unix timestamp value. I have converted the value into a date in order to apply my condition. The query is something along the lines:

select col1,
col2,
col3,
from table 
where trunc(to_date('01.01.1970', 'dd.mm.yyyy') + numtodsinterval(create_date,'SECOND')) BETWEEN '01.12.2023' and '31.12.2023'

The problem is that when i run the query i get the ORA-01843: not a valid month 01843. 00000 - "not a valid month"

I cannot figure out why and where is the problem. Any help is greatly appreciated.

Thank you

Answer 1

The problem is probably on the right-hand side of the comparison with BETWEEN '01.12.2023' and '31.12.2023' and not the left. '01.12.2023' and '31.12.2023' are string literals and are NOT date data-types.

Either:

• use DATE literals:

  BETWEEN DATE '2023-12-01' AND DATE '2023-12-31'
  

• or explicitly convert the strings to dates using TO_DATE:

  BETWEEN TO_DATE('01.12.2023', 'DD.MM.YYYY') and TO_DATE('31.12.2023', 'DD.MM.YYYY')
  

Alternatively, you can convert the right-hand side to a unix timestamp:

WHERE create_date >= (DATE '2023-12-01' - DATE '1970-01-01') * 24 * 60 * 60
AND   create_date <  (DATE '2024-01-01' - DATE '1970-01-01') * 24 * 60 * 60

Which would let you use an index on the create_date column.