CODEWITHSUNDEEP
sqloracle-databasedate
fanjavaid
fanjavaid

Reputation: 1738

How to select specific date in Oracle

I want to select just 1st and 20th date in Oracle Database. Here is my query.

SELECT INVOICE_DATE 
FROM invoice_debt@stilng1_lngrpt2 
WHERE INVOICE_DATE = TO_DATE('01', 'DD')
AND INVOICE_DATE = TO_DATE('20','DD')
AND INVOICE_DATE > TO_DATE('30-12-2010', 'DD-MM-YYYY')
AND INVOICE_DATE IS NOT NULL GROUP BY INVOICE_DATE 
ORDER BY INVOICE_DATE DESC;

My query run this :

  1. Select just 1st date
  2. Select just 20th date
  3. Select date > 30-12-2010

But nothing records returned.

I try using EXTRACT to get Day of DATE, but i cannot using WHERE clause.

SELECT 
EXTRACT(DAY FROM TO_DATE(INVOICE_DATE, 'DD-MM-YY')) as DAY_EXTRACT 
FROM invoice_debt@stilng1_lngrpt2;

Please help me thank you. :)

Upvotes: 5

Views: 108534

Answers (2)

psaraj12
psaraj12

Reputation: 5072

Use the OR condition for dates since you cannot have same invoice_date to be 1st day and 20th Day and also use to_char for the invoice date since to_date would return only one date

SELECT INVOICE_DATE 
FROM invoice_debt@stilng1_lngrpt2 
WHERE (to_char(INVOICE_DATE, 'DD')='01'
OR to_char(INVOICE_DATE,'DD')='20')
AND INVOICE_DATE > TO_DATE('30-12-2010', 'DD-MM-YYYY')
AND INVOICE_DATE IS NOT NULL GROUP BY INVOICE_DATE 
ORDER BY INVOICE_DATE DESC;

Upvotes: 4

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

EXTRACT(DAY FROM TO_DATE(INVOICE_DATE, 'DD-MM-YY'))

That makes no sense. You are converting a DATE to a DATE. I hope INVOICE_DATE column is DATE data type. So just extract the day from it -

extract(day from INVOICE_DATE)

You need an OR condition for the 1st/20th day selection, and an AND condition to filter the dates with condition TO_DATE('30-12-2010', 'DD-MM-YYYY').

But, you need to make sure your query uses an efficient execution plan.

  1. Using function TO_CHAR over the date coumn will suppress any normal index usage.

You need to have a function-based index on to_char(INVOICE_DATE, 'DD')

WHERE (to_char(INVOICE_DATE, 'DD')='01' 
         OR  to_char(INVOICE_DATE,'DD')='20')
      AND  INVOICE_DATE > TO_DATE('30-12-2010', 'DD-MM-YYYY')) 
      AND  INVOICE_DATE IS NOT NULL
  1. Since you only want to get dates greater than year 2010, I think specifying a RANGE will use the index on the date column to go for a INDEX RANGE SCAN.

You could try these suggestions and examine the explain plan.

Upvotes: 1

Related Questions