How to use 'Between' operator for DATEs in Teradata?

Question

I want to restrict the rows I retrieve by using 'between' for two dates.

The 'saledate' column I used has the following infomation

SALEDATE DATE FORMAT 'YYYY-MM-DD' NOT NULL

The code I used:

SELECT *
FROM trnsact
WHERE saledate BETWEEN '2005-01-01' AND '2005-06-30';

And then I got an error 'Error Code - 3535 Error Message - [Teradata Database] [TeraJDBC 15.10.00.09] [Error 3535][SQLState 22003] A character string failed conversion to a numeric value.'

I also tried with DATE:

SELECT *
FROM trnsact
WHERE saledate BETWEEN DATE '2005-01-01' AND DATE '2005-06-30';

But end up with another error Error Message - [Teradata Database] [TeraJDBC 15.10.00.09] [Error 3706] [SQLState 42000] Syntax error: Invalid DATE Literal.

Thanks for your help

Answer 1

You need to use DATE literals:

SELECT *
FROM trnsact
WHERE saledate BETWEEN DATE '2005-01-01' AND DATE '2005-06-31';

Answer 2

try below query for teradata. It's too late though.

SELECT *
FROM trnsact
WHERE saledate BETWEEN 
    to_date('2005-01-01','YYYY-MM-DD') AND 
    to_date('2005-06-30','YYYY-MM-DD') ;