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?

Solution 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

Solution 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.

Comments

[Ni_Tempe]

I copied one of the cell values and it is 8/1/2017

[dnoeth]

@Ni_Tempe: When you copy a cell value you probably mean SQL Assistant which formats the date based on local settings. It's recommended to switch to Standard SQL format YYYY-MM-DD in Tools-Options-Data Format-Display dates in this format

[dnoeth]

There's only one reliable way to write a date, using a date literal, date 'yyyy-mm-dd' and your 1st method simply doesn't use this format.

[Ozkan Serttas]

Thank you for the input. I tried this way WHERE saledate = date'2014-08-01' got no data this time. Is this because my date column use / between year/month/day ? I do not know why I am still not getting the correct result with this date literal.

[Vityata]

Thumbs up for the answer! :)