Hi Everyone,
I am new to Qilk Sense and having challenge trying to create a SQL script that will only load records where it Effective Date <= SYDATE
The following is my script
LOAD `sls_tty_id`,
`bsn_ar_grp_id`,
`eff_ts`,
`xpry_ts`,
`sls_tty_xtnd_nm`,
`bsn_ar_grp_nm`,
`st_bag_cust_sgm_cd`,
`st_bag_cust_sgm_nm`,
`st_bag_sls_cvrg_sgm_cd`,
`st_bag_sls_cvrg_sgm_nm`;
SQL SELECT DISTINCT `sls_tty_id`,
`bsn_ar_grp_id`,
`eff_ts`,
`xpry_ts`,
`sls_tty_xtnd_nm`,
`bsn_ar_grp_nm`,
`st_bag_cust_sgm_cd`,
`st_bag_cust_sgm_nm`,
`st_bag_sls_cvrg_sgm_cd`,
`st_bag_sls_cvrg_sgm_nm`
FROM HIVE.`ea_shared`.`st_bag_cust_pfl_dmnsn_rw_ext`
WHERE (Date(`eff_ts`, 'M/D/YYYY h:mm:ss TT') <= Date(Today(2),'M/D/YYYY'));
But I am getting the following error that Today is a invalid function
Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 80, ErrorMsg: [Hortonworks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: SemanticException [Error 10011]: Line 12:53 Invalid function 'Today'
Even if I hardcode a date, I end up loading 0 records
WHERE (Date(`eff_ts`, 'M/D/YYYY h:mm:ss TT') <= Date('10/17/2017 12:00:00 AM','M/D/YYYY h:mm:ss TT'));
Would appreciate if the community can share your experience working with dates in your data loader scripts.
