PL SQL data is in numeric format (20211023) i want to use the where clause on date column 30days-CodePudding

Select * from Table where date >='20210911'   30days

the date is in numeric format and what to pull the records for a specific date 30days of specific date

Could you please help

CodePudding user response：

Uh. Never store dates into any other datatype column but DATE. Now you first have to "convert" it, then do the arithmetic.

select * 
from some_table
where to_date(date_column, 'yyyymmdd') > date '2021-09-11'   30
                            --------
            apply format mask that matches data in that column

Hope (should I say pray?) that all values represent valid dates. Nobody prevents you to store e.g. 20228579 into it, and - applying to_date to it - results in

SQL> select to_date('20228579', 'yyyymmdd') from dual;
select to_date('20228579', 'yyyymmdd') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

Once again, bad, BAD idea!

CodePudding user response：

how to apply between clause ( where date_column between date '2021-09-11' and date '2021-09-11' 30

If you have an index on the column that you want to use then convert the value to a date then add 30 days to it and convert it back to a number:

SELECT *
FROM   Table_Name
WHERE  date_number BETWEEN 20210911
                   AND     TO_NUMBER(
                             TO_CHAR(
                               TO_DATE(20210911, 'YYYYMMDD')
                                 INTERVAL '30' DAY,
                               'YYYYMMDD'
                             )
                           )

If you don't have an index and want a simpler query then:

SELECT *
FROM   Table_Name
WHERE  TO_DATE(date_number, 'YYYYMMDD') BETWEEN DATE '2021-09-11'
                                        AND     DATE '2021-09-11'   INTERVAL '30' DAY

The best solution would be to convert your numeric column to a DATE column:

ALTER TABLE table_name ADD date_column DATE;
UPDATE table_name SET date_column = TO_DATE(date_number, 'YYYYMMDD');
ALTER TABLE table_name DROP COLUMN date_number;

Then:

SELECT *
FROM   Table_Name
WHERE  date_column BETWEEN DATE '2021-09-11'
                   AND     DATE '2021-09-11'   INTERVAL '30' DAY

db<>fiddle here