sql teradata filtering on date - database version Teradata 15.10.06.02 and provider version Teradata.Net 15.11.0.0

Question

my table has a date column. its data type is date. I confirmed it by going to table name>>columns and it says MTH_END_DT [DATE, Not NULL]

I want to filter my data for a particular date. If I put a condition where MTH_END_DT = '6/1/2018' I get an error select failed [3535] A character string failed conversion to a numeric value.

I followed this page. I used where MTH_END_DT = date '6/1/2018' and i get an error syntax error invalid date literal

I tried where cast(timestamp_column as date) = date '2013-10-22'; something like this and it throws error too

How should i filter my data?

Answer 1

There's only one reliable way to write a date, using a date literal, date 'yyyy-mm-dd'

where MTH_END_DT = DATE '2018-06-01'

For a Timestamp it's

TIMESTAMP '2018-06-01 15:34:56'

and for Time

TIME '15:34:56'

In SQL Assistant it's recommended to switch to Standard SQL format YYYY-MM-DD in Tools-Options-Data Format-Display dates in this format

Answer 2

I did have the similar problem when I was filtering a particular date for my query with Teradata. First method I tried was putting 'DATE' term as the following:

WHERE saledate = DATE'04/08/01' but this did not solve the problem.

I then used an approach I stumbled upon when surfing, finally it worked.

WHERE extract(year from saledate)=2004 AND extract(MONTH from saledate)=8 AND extract(DAY from saledate)= 1 source

I think this really should not be this long, but it worked.