Search code examples
oracledatetimedate-formattingto-char

SQL Developer returning incorrect date when formatted using TO_CHAR function

I am using a query to retrieve some data from my table based on certain conditions. Query is mentioned below.

The same format is specified when I am using TO_CHAR function to display the empcheckin time

TRIED:

  1. Set the default Date Format in (Tools>Preferences>Database>NLS>Date Format) as DD-MON-YY HH24:MI:SS
  2. Displaying the data in the same format using query using TO_CHAR function as TO_CHAR(E.EMPCHECKIN, 'DD-MON-YY HH24:MM:SS') <= TO_CHAR('24-APR-24 11:35:00')
  3. QUERY: SELECT L.LOCKUNLOCKID, E.EMPID, TO_CHAR(E.EMPCHECKIN, 'DD-MON-YY HH24:MM:SS'), S.N_SATOFF, e.empcheckin FROM LOCKUNLOCKEMPLDAP L JOIN EMPLOGINDTLS E ON E.EMPID=L.EMPID JOIN SATOFF S ON S.EMPID=L.EMPID WHERE TRUNC(L.PROCESS_DATE)=TRUNC(SYSDATE) AND TRUNC(E.EMPCHECKIN)>='22-APR-24' AND TRUNC(E.EMPCHECKIN) != E.EMPCHECKIN ORDER BY E.EMPID;

EXPECTED OUTPUT: Same data in both the columns of table i.e., Column 1 (e.empcheckin) | Column 2 ( TO_CHAR(E.EMPCHECKIN, 'DD-MON-YY HH24:MM:SS') 24-APR-24 11:42:28 | 24-APR-24 11:42:28

ACTUAL OUTPUT: [Refer this image for query result in SQL Developer]: https://i.sstatic.net/LexAX.png Values are not same there is a difference of 40 mins.

Here as you can observe there quite a difference between the two values of empcheckin. What may be causing this and how can I fix it?

Also side quest: Help me write the condition such that I can compare the timestamp in EMPCHECKIN to <SYSDATE>11:35:00 I tried using TO_CHAR function also but I am not sure if it can compare dates correctly. TO_CHAR(E.EMPCHECKIN, 'DD-MON-YY HH24:MM:SS') <= TO_CHAR('24-APR-24 11:35:00')

Solution

  • ... TO_CHAR(E.EMPCHECKIN, 'DD-MON-YY HH24:MM:SS') ...

    You've used the month-number MM format element in 'DD-MON-YY HH24:MM:SS' instead of the minutes MI element. That's why the 'minutes' part in your output is showing as 04 - as that is the month number for April.

    It should be:

    TO_CHAR(E.EMPCHECKIN, 'DD-MON-YY HH24:MI:SS')

    You are also doing:

    WHERE TRUNC(L.PROCESS_DATE)=TRUNC(SYSDATE)
AND  TRUNC(E.EMPCHECKIN)>='22-APR-24'

    You don't really want to truncate the column values, because applying a function to the column will stop any plain indexes on those being used. For the second line, if that condition is true truncated then it will be true without. For the first line that will also be the case if process_date can't be in the future; if it can then do a range check instead.

    WHERE L.PROCESS_DATE >= TRUNC(SYSDATE)
AND L.PROCESS_DATE < TRUNC(SYSDATE) + 1 -- only needed if process_date can be in the future 
AND E.EMPCHECKIN >= DATE '2024-04-22'

    compare the timestamp in EMPCHECKIN to <SYSDATE>11:35:00

    If you want an upper bound on that check - which it looked like your fixed-value attempt is doing - then you can do:

    AND E.EMPCHECKIN <= TRUNC(SYSDATE) + INTERVAL '11:35' HOUR TO MINUTE

    or

    AND E.EMPCHECKIN < TRUNC(SYSDATE) + INTERVAL '11:35:01' HOUR TO SECOND