How to change date format for the condition which involves a calculation ? 1

[estersita]

Dear Experts,


I want to set the condition where dte_received is between today date and the date 12 months ago.So far I tried the following statement:
WHERE dte_received between (sysdate,-12) and sysdate; (at which System generated an error message)

I run the following:
SELECT SYSDATE FROM dual (which looks like 5/7/2018 10:49:07 AM)
&
SELECT dte_received FROM MYTABLE (looks like 20171213)


How can I change dte_received format in order my statement above would work?

Thank you in advance!

Estersita

 

[johnherman]

suggest you check the Oracle TO_DATE function.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm

You will need to reformat the SYSDATE, your dte_received, or both in order to compare "apples to apples".

As of now, you are comparing an Oracle "date" data type to the data type of dte_received, which is either a number or a character string, but NOT an Oracle "date".

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[carp]

Try

WHERE dte_received BETWEEN add_months(sysdate,-12) AND sysdate

This assumes that dte_received is a DATE datatype.

 

[estersita]

carp,

I tried to do it but get an error ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Actually for SYSDATE it is for sure ( 5/7/2018 10:49:07 AM) . As for dte_received it looks like a string 20171213.
I tried to convert but failed. This is my problem as I am new in Oracle.

Could you please give me a hand?

Thank you for you help!

Estersita

 

[Beilstwh]

WHERE to_date(dte_received,'YYYYMMDD') BETWEEN add_months(TRUNC(sysdate),-12) AND TRUNC(sysdate)

The TRUNC removes the time element and the older date must be first in the between clause

Bill
Lead Application Developer
New York State, USA