Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: February 08, 2019 - 7:26 pm UTC

Last updated: February 14, 2019 - 4:25 am UTC

Version: 18.3.0

Viewed 1000+ times

You Asked

Hello, Ask tom Team. I want to make a procedure (if it's the best way) to be execute every day based on two possible dates. I thing I have to fill two variables first.


--Gettings dates

If today's date is <= 15th day of the current month then
FROM_DATE = 1st day of last month
UNTIL_DATE = Today's date

If today's date is > 15th day of the current month then
FROM_DATE = 1st day of current month
UNTIL_DATE = Today's date

--Returning results
select a, b ...
from table
where date_loaded >= FROM_DATE and date_loaded <= UNTIL_DATE -- (I do not know if between would be better here).

What is the best way to do this?

Thanks in advanced.

Regards,

and Connor said...

This should do the trick

from   MY_TABLE
where  DATE_COL < trunc(sysdate)+1
and    DATE_COL >=
  case 
    when to_number(to_char(sysdate,'DD')) <= 15 then
      trunc(sysdate-20,'MM')
    else
      trunc(sysdate,'MM')
  end


Notes:

"DATE_COL < trunc(sysdate)+1" is *inclusive* of today's data, remove the "+1" to be exclusive

The case statement says:

If I'm less then 15 days into the current month, then remove 20 days (which puts me *somewhere* in last month, and then truncate down the 1st of that month)

otherwise just truncate down to the start of the current month.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

A reader, February 11, 2019 - 11:01 am UTC

Thanks, Connor. It's been really helpful.

Anything to have in mind about performance when dealing with dates in the where condition over huge tables (thousands of millions rows)?

Thanks in advance.
Connor McDonald
February 12, 2019 - 1:05 am UTC

Nothing different to any other condition.

The most common "problem" is when people make assumptions about dates, so they write things like:

where date_col > '01-jan-00'

ie, a string not a date. That's a bad idea.

or they revert to using strings entirely:

where to_char(date_col) > '01-jan-00'

That's a *really* bad idea.

Review

Geraldo Peralta, February 13, 2019 - 4:02 pm UTC

Ok. Thanks for the reply.

What about the index if the date column has the time component?

Thanks in advanced.


Chris Saxon
February 13, 2019 - 4:39 pm UTC

A date in Oracle Database always has a time component. By convention a "date with no time" has the time set to midnight.

As long as you have no functions on the date column, i.e. you're NOT doing trunc ( date_col ), the database can use the index just fine.

Review

Geraldo Peralta, February 13, 2019 - 4:48 pm UTC

Good.

Thanks for the quick response.
Connor McDonald
February 14, 2019 - 4:25 am UTC

Glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library