I have a column with a timestamp and I only want to return the date portion using BETWEEN. My query isn't returning any data.
Where cast(rec_crt_dt as Date FORMAT 'YYYYMMDD') between 2016-11-1 and 2017-11-1;
The way it is written here it looks like arithmetic, i.e. 2016-11-1 looks like the number 2004.
Try: between DATE'2016-11-01' and DATE'2017-11-01'.
Also: between 20161101 and 20171101 -- might work, depending on your mode.
Got an error "expected something like 'AND' keyword between the 'Date' keyword and the integer '2016'
Where cast(rec_crt_dt as Date FORMAT 'YYYYMMDD') BETWEEN Date 2016-11-01 AND Date 2017-11-01;
When you compare dates and timestamps the timestamp is automatically casted to a date:
Where rec_crt_dt between DATE '2016-11-01' and DATE '2017-11-01';
But instead of casting the column to date it might be better to use timestamps literals:
Where rec_crt_dt >= TIMESTAMP '2016-11-01 00:00:00' and rec_crt_dt < TIMESTAMP '2017-11-02 00:00:00';
Btw, do you really want to include rows from '2017-11-01'?
Regarding the error message, date (literals) must be enclosed in single quotes:
Date '2016-11-01' instead of Date 2016-11-01