Invalid date format in datatype column in BODS job to Oracle

Question

I am using SAP BODS and I am trying to fetch data from an ORACLE server using SQL query transformation. Now The table has a column named latest_changed_date which is a datetime column. I only want yesterday and current day data from that table. Now since the column is datetime, I need to convert it to date, but when I am using to_date function I get the following error.

SELECT *
FROM ABC.TEST
WHERE TO_DATE(LATEST_CHANGED_DATE) = TO_DATE(SYSDATE-1)

The database error message is

ORA-01843: not a valid month

I tried giving date format in TO_DATE condition as below:

SELECT *
FROM ABC.TEST
WHERE TO_DATE(LATEST_CHANGED_DATE,'YYYY-MM-DD') >= TO_DATE(SYSDATE-1,'YYYY-MM-DD')

Here I got the error:

date format picture ends before converting entire input string

I used trunc function also and again got either:

not a valid month

or

inconsistent datatypes: expected NUMBER got DATE

Below is a sample data for the column. I just need data for current and day before data from the column.

Update: I think the main issue is that I am not able to determine the proper datatype for the column in the source table and currently I don't have an option to determine that.

Answer 1

this will work:

SELECT *
FROM ABC.TEST
where sysdate-LATEST_CHANGED_DATE<=sysdate-(sysdate-2);

for example take this:

ALTER SESSION SET NLS_DATE_FORMAT = ' DD-MON-YYYY HH24:MI:SS';
SELECT * FROM d061_dates ;

 03-DEC-2018 17:44:38
 25-AUG-2018 17:44:42
 30-AUG-2018 17:44:46
 01-DEC-2018 17:44:49
 02-DEC-2018 17:46:31

SELECT * FROM d061_dates
where sysdate-a<=sysdate-(sysdate-2);

 03-DEC-2018 17:44:38
 02-DEC-2018 17:46:31

you have to take sysdate minus on both sides to get comparision by a number which is less than equal to 2 to get day and day before yesterday and its giving the correct output.

thank you!!!!!!!!!!!!!

Answer 2

Rather than trying to implicitly cast your dates to strings and convert them back using TO_DATE( string_value, format_model ) you can use TRUNC() to truncate SYSDATE to the start of the day:

SELECT *
FROM   ABC.TEST
WHERE  LATEST_CHANGED_DATE >= TRUNC( SYSDATE-1 )