netezza compare char date in YYYY-MM-DD format to parameter date in yyyy-mm-dd format

Question

I am querying a file with dates in YYYY-MM-DD format but are formatted as character. If I just run a simple query like with WHERE DATE_IN_FILE >= '2022-01-31' that works. However I need to make that date a parameter so it does not have to be hardcoded. I've created the parameter like this: select trim(to_char(to_date(trim(date_today),'yyyymmdd')+1,'yyyy-mm-dd')) into v_eff_dt from model_input_param date_today is formatted as character also in yyyymmdd format, ex. 20220630. So I want to compare date_in_file to v_eff_dt. I've tried multiple ways to do this and nothing seems to work. Here's some of what I've tried:

1. to_date(DATE_IN_FILE, 'YYYYMMDD') <= to_date(V_EFF_DT,'YYYYYMMDD')
2. DATE_IN_FILE <= V_EFF_DT
3. to_date(DATE_IN_FILE,'YYYY-MM-DD') <= to_date(V_EFF_DT,'YYYY-MM-DD')

Hoping to get some help. thanks

Answer 1

Lets say you have these data in (external) file

cat /tmp/dat
2022-01-02
2022-02-02
2022-04-02
2023-03-12
2023-05-22
2023-06-03

Now in Netezza you can read these on the fly (or create an external table and then read)

select * from external'/tmp/dat' (date_in_file date) ;
 DATE_IN_FILE
--------------
 2022-01-02
 2022-02-02
 2022-04-02
 2023-03-12
 2023-05-22
 2023-06-03
(6 rows)

Now you can store your parameter in a table and then filter like this

-- or insert 
create table v_eff_dt as 
select duration_add(current_date, 1::numeric(8,0));
INSERT 0 1

with tempfile(date_in_file) as (
  select * from external'/tmp/dat' (date_in_file date) 
) select * 
  from tempfile, v_eff_dt 
  where tempfile.date_in_file <= dt;

 DATE_IN_FILE |     DT
--------------+------------
 2022-01-02   | 2022-07-09
 2022-02-02   | 2022-07-09
 2022-04-02   | 2022-07-09
(3 rows)

But this depends on v_eff_dt always having 1 row to be effective. Its much better to just do

select * 
from external'/tmp/dat' (date_in_file date) 
where date_in_file <= duration_add(current_date, 1::numeric(8,0))

 DATE_IN_FILE
--------------
 2022-01-02
 2022-02-02
 2022-04-02
(3 rows)