Markus Markus - 5 months ago 16
SQL Question

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?

Answer

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...

Comments