CODEWITHSUNDEEP
sqloracle-database
Christopher
Christopher

Reputation: 25

Oracle SQL: Query YTD for every year

How would I write an Oracle SQL query to pull every record from a table where the date is less than or equal to this day of that year?

For example:

My table:

date_field
01/01/2010
12/31/2010
01/01/2021
07/21/2021

My goal if the query was ran on 07/21/2021

date_field
01/01/2010
01/01/2021
07/21/2021

Upvotes: 1

Views: 191

Answers (3)

Alex Poole
Alex Poole

Reputation: 191235

Another option is to extract and compare the months and days as numbers:

select date_field
from your_table
-- any earlier month
where extract(month from date_field) < extract(month from sysdate)
or (
  -- any earlier or same day in this month
  extract(month from date_field) = extract(month from sysdate)
  and extract(day from date_field) <= extract(day from sysdate)
)
order by date_field

db<>fiddle

With this or @Gordon's string approach, you might benefit from function-based indexes, though unless your data is really skewed towards the end of the year they probably wouldn't help here.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can convert the month/day to a string and compare:

select t.*
from t
where to_char(date_field, 'MMDD') < to_char(sysdate, 'MMDD');

Upvotes: 2

Christopher
Christopher

Reputation: 25

This is a potential solution.

  SELECT date_field
        FROM sample_data
       WHERE date_field <=
             TO_DATE (
                  (   TO_CHAR (SYSDATE, 'MM/DD/')
                   || EXTRACT (YEAR FROM date_field))
                     ,'MM/DD/RRRR')

Upvotes: 0

Related Questions