View original (French)

Date format issue ????

plogos Posted messages 1 Status Member -  
jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   -
Hello,

First of all, I need to say that I have this
SQL> select sysdate from dual;

SYSDATE
--------
23/07/20

I have a result that I cannot interpret: the first query returns nothing while the second one, for which I commented out the date criteria, returns data. Does anyone have an idea?

SELECT ecdat, ctct, sensod, sum(mtdeb), sum(mtcre), flag, brecr
FROM KTEST, KIDFBUD
WHERE KTEST.SCSO||''= 'XX'
AND KTEST.TJCOD = NVL('BQ', KTEST.TJCOD)
AND KTEST.OPC = NVL('CBBV', KTEST.OPC)
AND KTEST.DATEXE = 2020
AND KTEST.PEC = 802
AND KTEST.ECDAT <= TO_DATE('10062020', 'DDMMYYYY')
AND KTEST.ECDAT >= TO_DATE('10062020', 'DDMMYYYY')
AND KTEST.SCSO = KIDFBUD.SCSO
AND KTEST.ICBUD = KIDFBUD.ICBUD
AND KTEST.FLAG != '8'
AND KTEST.FLAG != '5'
AND KTEST.FLAG != '9'
AND KTEST.CTCT||'' IN ('L', 'A', 'P')
GROUP BY ecdat, ctct, sensod, flag, brecr;

No rows selected

SELECT ecdat, ctct, sensod, sum(mtdeb), sum(mtcre), flag, brecr
FROM KTEST, KIDFBUD
WHERE KTEST.SCSO||''= 'XX'
AND KTEST.TJCOD = NVL('BQ', KTEST.TJCOD)
AND KTEST.OPC = NVL('CBBV', KTEST.OPC)
AND KTEST.DATEXE = 2020
AND KTEST.PEC = 802
--AND KTEST.ECDAT <= TO_DATE('10062020', 'DDMMYYYY')
--AND KTEST.ECDAT >= TO_DATE('10062020', 'DDMMYYYY')
AND KTEST.SCSO = KIDFBUD.SCSO
AND KTEST.ICBUD = KIDFBUD.ICBUD
AND KTEST.FLAG != '8'
AND KTEST.FLAG != '5'
AND KTEST.FLAG != '9'
AND KTEST.CTCT||'' IN ('L', 'A', 'P')
GROUP BY ecdat, ctct, sensod, flag, brecr;

ECDAT C S SUM(MTDEB) SUM(MTCRE) F BRECR
-------- - - ---------- ---------- - ----------
10/06/20 L C 4578.79 1
10/06/20 L D 4578.79 1
21/07/20 L C 1224.39 1
21/07/20 L D 1224.39 1

1 answer

  1.
    jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 833
     
    Hello,

    Since you have 
     AND KTEST.ECDAT <= TO_DATE('10062020','DDMMYYYY') AND KTEST.ECDAT >= TO_DATE('10062020','DDMMYYYY')


    How... can your field ECDAT be both greater than (or equal to) AND less than (or equal to) the same date?
    You might as well just write 
     AND KTEST.ECDAT = TO_DATE('10062020','DDMMYYYY')


    Your other issue seems to concern the "format"
    Your field ECDAT appears to be in the format DD/MM/YY
    however, you have put 4 Y's

    It also seems strange to me that the date is displayed in this format DD/MM/YY... it seems like you haven't used a DATE type field in your database but rather a VARCHAR ...
    However... a DATE, in a database, should be stored in a DATE type field!!

    In any case, you could try 
     TO_DATE(KTEST.ECDAT, 'DDMMYY') = '100620'


    .
    --
    Best regards,
    Jordane
    0