On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,517 Users
  • 2,269,752 Discussions
  • 7,916,764 Comments

Discussions

Issue with timestamp and date comparison

Vicky007
Vicky007 Member Posts: 60
edited May 2, 2020 5:37AM in SQL & PL/SQL

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

ThanksCapture.PNG

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 ?

Tagged:
Vicky007

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Apr 26, 2020 8:19PM Answer ✓

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

    Vicky007

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Apr 26, 2020 8:02PM

    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
    BrunoVroman BelgiumMember Posts: 1,848 Silver Crown
    edited Apr 24, 2020 8:21AM

    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
    mathguy Member Posts: 11,039 Black Diamond
    edited Apr 24, 2020 8:17AM
    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
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Apr 26, 2020 8:19PM Answer ✓

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

    Vicky007
  • mathguy
    mathguy Member Posts: 11,039 Black Diamond
    edited Apr 24, 2020 8:34AM
    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.

    Vicky007
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Apr 27, 2020 3:40AM

    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
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Apr 26, 2020 8:11PM

    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.

    Vicky007
  • Wyche414
    Wyche414 Member Posts: 1
    edited Apr 28, 2020 1:13AM