Skip to Main Content


Issue with timestamp and date comparison

Vicky007Apr 24 2020 — edited May 2 2020

Hi ,

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" , 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 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 ?

This post has been answered by Paulzip on Apr 24 2020
Jump to Answer
Post Details
Added on Apr 24 2020