I am trying to compare the timestamp column using below query.
select * from all_biller_report b where (trunc(b.DATE_TIME) between ( LAST_DAY((TRUNC(SYSDATE) - INTERVAL '1' DAY) - INTERVAL '1' MONTH) + INTERVAL '1' DAY ) and (CURRENT_DATE - INTERVAL '1' DAY));
Short description is - it fetches table data between start of the month and current date - 1
Data is loaded in table has DATE_TIME ( timestamp(3) ) column.
CSV file has column value as "22-APR-20 00.00.00.898" , once loaded using sqlldr using ( Date_Time timestamp "DD-MON-YYYY HH24.MI.SS" ) ,
Also , i have set session date format as below
execute immediate 'alter session set nls_date_format=''dd-mm-yy hh24:mi:ss''';
execute immediate 'alter session set NLS_TIMESTAMP_FORMAT=''dd-mm-yy hh24:mi:ss''';
above query does not return data even if 22 April is between 1St April to 23 April.
it shows data as "22-APR-20 12.00.00.898000000 AM" in SQL DEVELOPER
I am not able to figure out what is the issue with it , also can someone help me with it in detail ?
Any quick feeds ?