CODEWITHSUNDEEP
sqloracle-databasedate
Markus
Markus

Reputation: 1292

Date column as "YYYYMMDD" string - how to select all rows >= 30 days in the past?

I have a table which contains a VALID_TO column as VARCHAR2. This column has date strings in format "YYYYMMDD", e.g. "20160624".

ID     ARTICLE_NUMBER   STORE_ID   COUNTRY   VALID_TO
----------------------------------------------------------------
100    111              22         AT        20160624
...

What I need is a SELECT that gives me all rows having a VALID_TO date that is 30 days old or older. Any idea how to achieve it?

Upvotes: 0

Views: 525

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Seems pretty straightforward; find the date 30 days ago, convert it to the same format, and use that for your filter:

where valid_to <= to_char(sysdate - 30, 'YYYYMMDD')

Simple demo using a CTE to generate 40 dates:

with t42 (valid_to) as (
  select to_char(trunc(sysdate) - level, 'YYYYMMDD') from dual connect by level <= 40
)
select valid_to
from t42
where valid_to <= to_char(sysdate - 30, 'YYYYMMDD');

VALID_TO
--------
20160525
20160524
20160523
20160522
20160521
20160520
20160519
20160518
20160517
20160516
20160515

 11 rows selected

While your stored format makes this kind of search do-able, it's still better to store dates as actual dates rather than as string. There's little stopping you putting an invalid value in there, e.g. 20161357...

Upvotes: 1

Related Questions