Issue with timestamp and date comparison

Vicky007

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 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

Thanks

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 ?

Paulzip

I think you need to load the data using DD-MON-RRRR HH24.MI.SS, otherwise you'll get 20 AD (CE) rather than 2020 AD (CE) - which I suspect might be your problem. 20 AD won't appear between your data ranges.

Here's a demo of what I mean :

select extract(year from to_date('22-APR-20', 'DD-MON-YYYY')) year1,

       extract(year from to_date('22-APR-20', 'DD-MON-RRRR')) year2

from dual

/

YEAR1  YEAR2

-----  -----

   20   2020

You could quickly test this hypothesis

select extract(year from date_time) yr

from all_biller_report

where date_time < date '0100-01-01' -- In two digit years AD / BCE

Bruno makes a good point on how you'd achieve your date filtering without applying a function to your column (which could affect performance such as inhibit index usage).

Gaz in Oz

SQL Developer does NOT honor setting NLS DATE/TIMESTAMP ALTER SESSION statements, whether written as an exececute immmediate (which is actually a PL/SQL command which should have a begin/end; pair) or straight "execute immediate"... or just plain "alter session set".

You need to go to

Tools -> Preferences -> Database -> NLS

and change it there.

BrunoVroman

Hello Vicky,

avoid manipulating the data when you have to compare to constants... For example: instead of doing WHERE TRUNC( mynumber ) <= 123 you might ask WHERE mynumber < 124

In your case you work on "b.date_time" when it is not necessary (difference: TRUNC( mycol ) ... implies to compute for each and every candidate row, mycol ... doesn't require computation but only a comparison)

Also I see that you like the syntax of intervals, I don't... I prefer the "old way" of "date arithmetic": date + N means date + N days... So It seems that your request is equivalent to something like

SELECT b.*

  FROM all_biller_report b

  WHERE b.date_time >= TRUNC( sysdate, 'MM' )

    AND b.date_time <= sysdate - 1

;

(or maybe: ... AND b.date_time < TRUNC( sysdate ) ?)

Best regards,

Bruno Vroman.

(Edited: I'm not used to work with time zones so for me sysdate is the same as current date... But this needs most probably some extra vigilance, as you use "CURRENT_DATE" and not "SYSDATE" ((but why do you have 1 time sysdate and the other time current_date?)))

mathguy

Gaz in Oz wrote:SQL Developer does NOT honour setting NLS DATE/TIMESTAMP ALTER statements.You need to go toTools -> Preferences -> Database -> NLSand change it there.

I am pretty sure it does, I use it all the time. Why do you think it doesn't?

Paulzip

I think you need to load the data using DD-MON-RRRR HH24.MI.SS, otherwise you'll get 20 AD (CE) rather than 2020 AD (CE) - which I suspect might be your problem. 20 AD won't appear between your data ranges.

Here's a demo of what I mean :

select extract(year from to_date('22-APR-20', 'DD-MON-YYYY')) year1,

       extract(year from to_date('22-APR-20', 'DD-MON-RRRR')) year2

from dual

/

YEAR1  YEAR2

-----  -----

   20   2020

You could quickly test this hypothesis

select extract(year from date_time) yr

from all_biller_report

where date_time < date '0100-01-01' -- In two digit years AD / BCE

Bruno makes a good point on how you'd achieve your date filtering without applying a function to your column (which could affect performance such as inhibit index usage).

mathguy

Vicky007 wrote:
Also , i have set session date format as below

1. execute immediate 'alter session set nls_date_format=''dd-mm-yy hh24:mi:ss''';
2. execute immediate 'alter session set NLS_TIMESTAMP_FORMAT=''dd-mm-yy hh24:mi:ss''';

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'''; 

Why are you using execute immediate for this? ALTER SESSION is a SQL statement, why not execute it directly?

In any case, in SQL Developer, if you wrap your statements within BEGIN ... END  (or EXEC), they WILL change your date/timestamp format. (As you wrote them, not within an anonymous block, they will throw errors.)

You also mentioned that you loaded the timestamp values with the format "DD-MON-YYYY HH24.MI.SS"  Are you sure about that? I find it hard to believe that loading with that format would have preserved the fractional seconds. Are you sure your format model didn't have ".FF" at the end?

Now, to your question:

If you used the model "DD-MON-YYYY" as you say, and the input was "22-APR-20", then the 20 really means the year 20 A.D., not 2020. That would explain why you don't see those values in the output.

Inspect your CSV - does it show the year as "20" or as "2020"?  Then, when you load the data to the DB, make sure the format model matches the data in the CSV. You will be forced to use something like "DD-MON-RR ...."

The better solution is to have four-digit year in the CSV, if you have control over that.

Cookiemonster76

Your actual issue is that your timestamps aren't in the year 2020, they're in the year 20 (as in back when Jesus was alive):

SQL> SELECT to_char(to_date('24-04-20', 'DD-MM-YYYY'), 'DD-MM-YYYY') FROM dual;

TO_CHAR(TO_DATE('24-04-20','DD-MM-YYYY'),'DD-MM-YYYY')

------------------------------------------------------

24-04-0020

The format mask YYYY expects a 4 digit year and you're only supplying 2. So it assumes those 2 digits are the exact value.

You need to supply 4 digits or use RR:

SQL> SELECT to_char(to_date('24-04-2020', 'DD-MM-YYYY'), 'DD-MM-YYYY') FROM dual;

TO_CHAR(TO_DATE('24-04-2020','DD-MM-YYYY'),'DD-MM-YYYY')

--------------------------------------------------------

24-04-2020

SQL> SELECT to_char(to_date('24-04-20', 'DD-MM-RR'), 'DD-MM-YYYY') FROM dual;

TO_CHAR(TO_DATE('24-04-20','DD-MM-RR'),'DD-MM-YYYY')

----------------------------------------------------

24-04-2020

Gaz in Oz

On testing sqldev does honor changing the nls date/timestamp/timestam_tz format using alter session. Not sure how I got the notion it didn't.

Wyche414

same here. mysubwaycard