CODEWITHSUNDEEP
sqloracle-databasesysdate
James Macfarlane
James Macfarlane

Reputation: 1

SYSDATE without hardcoding dates into a report

I have the below query.

SELECT CUST_CODE,
       CHG_DATE,
       ACCSS_NUM,
       ACCSS_REF_NUM,
       ACCSS_REF_DES,
       CHG_CODE,
       CHG_QTY,
       CHG_RATE_CRNT,
       CHG_TOT,
       INVT_LEV1,
       QTY,
       ACCSS_DATE
FROM   E_ACCSS_H H  
WHERE  (SYSDATE) >= start_date
AND    (SYSDATE) < end_date+2
       H.comp_code='F1'
and    H.accss_stat='A'
AND    (CUST_CODE='TYS001')
group by
       CUST_CODE,
       CHG_DATE'
       ACCSS_NUM,
       ACCSS_REF_NUM,
       ACCSS_REF_DES,
       CHG_CODE,
       CHG_QTY,
       CHG_RATE_CRNT,
       CHG_TOT,
       INVT_LEV1,
       QTY,
       ACCSS_DATE

I am trying to the the data to look a the past 2 days based on the sysdate but getting parentheses errors. Please can you help?

Upvotes: 0

Views: 70

Answers (4)

Lay RT
Lay RT

Reputation: 1

Your error is on the where clause, each condition should be connected with an operator unless you are performing a functional call.

WHERE  SYSDATE >= start_date
AND    SYSDATE < end_date+2
       AND H.comp_code='F1'
       AND H.accss_stat='A'
       AND CUST_CODE='TYS001'

Upvotes: 0

MT0
MT0

Reputation: 168740

I am trying to the the data to look a the past 2 days

In Oracle, a DATE has both date and time components.

  1. If you want rows where the end_date is today, yesterday or the day before then you can use

    WHERE  end_date   >= TRUNC(SYSDATE - 2) 
AND    end_date   <  TRUNC(SYSDATE + 1)

  2. If you want to have the start_date in the same range then use the same filter replacing end_date with start_date.

  3. If you want it to both start and end within that range then use:

    WHERE  start_date >= TRUNC(SYSDATE - 2) 
AND    start_date <  TRUNC(SYSDATE + 1)
AND    end_date   >= TRUNC(SYSDATE - 2) 
AND    end_date   <  TRUNC(SYSDATE + 1)

    Or, if you have a CHECK constraint that start_date <= end_date you can simplify it to:

    WHERE  start_date >= TRUNC(SYSDATE - 2) 
AND    end_date   <  TRUNC(SYSDATE + 1)

  4. If you want the range to overlap with some point in today, yesterday or the day before then:

    SELECT CUST_CODE,
       CHG_DATE,
       ACCSS_NUM,
       ACCSS_REF_NUM,
       ACCSS_REF_DES,
       CHG_CODE,
       CHG_QTY,
       CHG_RATE_CRNT,
       CHG_TOT,
       INVT_LEV1,
       QTY,
       ACCSS_DATE
FROM   E_ACCSS_H
WHERE  start_date <  TRUNC(SYSDATE + 1)
AND    end_date   >= TRUNC(SYSDATE - 2)
AND    comp_code  =  'F1'
AND    accss_stat =  'A'
AND    CUST_CODE  =  'TYS001'

(Note: You are not using any aggregation functions like MAX, SUM, COUNT so the GROUP BY clause appears to be pointless. If you are using it to get rid of duplicates then use SELECT DISTINCT ... instead of GROUP BY.)

Upvotes: 1

d r
d r

Reputation: 7891

With no sample data, expected result - the only thing we could do is answer the question of how to select the data from last two days from SYSDATE.
Assuming that you have some date column in your table to be compared to the two day period and not dealing with time part of DATE datatype:

WITH     -- S a m p l e    D a t a :
    test_tbl (ID, DATE_COLUMN, SOME_OTHER_COLUMN) AS
        ( Select 1, DATE '2024-05-09', 'Something 1' From Dual Union All 
          Select 2, DATE '2024-05-10', 'Something 2' From Dual Union All 
          Select 3, DATE '2024-05-11', 'Something 3' From Dual Union All 
          Select 4, DATE '2024-05-12', 'Something 4' From Dual Union All 
          Select 5, DATE '2024-05-13', 'Something 5' From Dual Union All 
          Select 6, DATE '2024-05-14', 'Something 6' From Dual 
        )

--    S Q L :     (on 2024-05-12 as SYSDATE)
Select      *
From    test_tbl
Where   DATE_COLUMN >= Trunc(SYSDATE - 2) And DATE_COLUMN <= Trunc(SYSDATE)
        -- or, as an alternative, the condition below
        -- DATE_COLUMN Between Trunc(SYSDATE - 2) And Trunc(SYSDATE)

/*    R e s u l t : 
        ID DATE_COLUMN SOME_OTHER_COLUMN
---------- ----------- -----------------
         2 10.05.24    Something 2
         3 11.05.24    Something 3
         4 12.05.24    Something 4

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

Usually the condition is easier to read when you swap the fields. And you can use BETWEEN Try this one:

WHERE start_date BETWEEN SYSDATE-2 AND SYSDATE

Note, SYSDATE always contain the time part, i. e. the current time. Perhaps you are looking for

WHERE start_date BETWEEN TRUNC(SYSDATE-2) AND TRUNC(SYSDATE)

to get result for past two days at midnight.

Upvotes: 0

Related Questions