Need a help on writing a query to fetch the POs created in last one day

Toad for Oracle & DevOps Toolkit
1

Hi Experts,
I need to find the POs which are created yesterday i.e., between 20-Sep-2016 00:00:00 and 20-Sep-2016 23:59:59.
We are going to use the CREATION_DATE column of PO_HEADERS_ALL Table

Thanks in Advance,
Srivathsava

2

There are a couple of ways to do it:

select *
from po_headers_all
where creation_date between TO_DATE(‘20-Sep-2016 00:00:00’, ‘DD-Mon-YYYY HH24:MI:SS’)
and TO_DATE(‘20-Sep-2016 23:59:59’, ‘DD-Mon-YYYY HH24:MI:SS’);

– or –

select *
from po_headers_all
where trunc(creation_date) = TO_DATE(‘20-Sep-2016’, ‘DD-Mon-YYYY’);

– or, if you really care about yesterday, whatever that is —

select *
from po_headers_all
where trunc(creation_date) = trunc(sysdate - 1);

3

Hi John,

Actually I said just for understanding as yesterday. So I need query by using ‘SYSDATE’, not by directly entering the date.
Actually I tried. I got as below. Can you please look on to this and validate. Or else, let me know if there is any other simple way.

SELECT *
FROM PO_HEADERS_ALL poh
WHERE poh.last_update_date BETWEEN TO_DATE(TO_CHAR(SYSDATE-1,‘DD-MM-YYYY’)|| ’ 00:00:00’, ‘DD-MM-YYYY HH24:MI:SS’)
AND TO_DATE(TO_CHAR(SYSDATE-1,‘DD-MM-YYYY’)|| ’ 23:59:59’, ‘DD-MM-YYYY HH24:MI:SS’)

4

Hi Srivathsava,

As with many things Oracle, there’s a big “it depends”. It depends on the answers to questions like:

What is the data type of the LAST_UPDATE_DATE column?
Is the LAST_UPDATE_DATE column indexed?
Is this an ad-hoc one-shot query, on-demand, or scheduled?
How big is the table?
How many rows are expected to be returned?
What does the explain plan say about these queries?

…and many more. The answers to these are something that you will have to use to decide which query of John’s to use. Or, assuming indexing and other factors are OK, I might use something like:

SELECT *
FROM PO_HEADERS_ALL poh
WHERE poh.last_update_date BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE)-1/24/60/60;

Also, since this isn’t really a question about Toad, this forum isn’t an ideal place to ask it. But to be fair, I can’t find the SQL forum, either…

GL!

Rich

5

In that case, my third example will do what you need. Yours will work but it seems overly complicated to me. If all you care about is the date, not the time, then only compare the date and not the time.

There is one possible benefit to your query over mine - if the LAST_UPDATE_DATE is indexed, then your query will use the index and mine will not. That is because I put a TRUNC() around LAST_UPDATE_DATE in the where clause. But if there is no such index, I think mine is better because it’s easier to read…

Another way to write your query, which I find easier to read, would be:

SELECT *
FROM PO_HEADERS_ALL
WHERE last_update_date BETWEEN TRUNC(SYSDATE - 1)
AND TRUNC(SYSDATE) - (1/86400);

86400 is the number of seconds in a day.

When you put TRUNC() around a date, it throws away the time portion (makes it midnight)

6

Great minds think alike, John! And apparently ours do, too!

Rich

7

I was thinking the same thing.