5

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?

Ni_Tempe
  • 307
  • 1
  • 6
  • 20

3 Answers3

11

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

dnoeth
  • 59,503
  • 4
  • 39
  • 56
4

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.

Ozkan Serttas
  • 947
  • 13
  • 14
  • 3
    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. – dnoeth Sep 30 '18 at 21:34
  • 1
    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. – Ozkan Serttas Sep 30 '18 at 22:28
  • What's the exact datatype of `saledate`? `WHERE saledate = date'2014-08-01'` should work for `date` and also `timestamp` (it's automatically converted to a date for comparison). – dnoeth Oct 01 '18 at 07:38
0

It seems to me it’s most likely you have input the date format incorrectly? Maybe it includes a time by default.

For example

where MTH_END_DT = ‘2013-10-22-00:00:00:00’

Bboy9898
  • 11
  • 2
  • I copied one of the cell values and it is `8/1/2017` – Ni_Tempe Jun 15 '18 at 18:53
  • @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 Jun 15 '18 at 19:01