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

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?

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

sql - query to return specific date from teradata timestamp(6), You may try like this:- sel * from table A where date = date '2013-10-22';. Since in ANSI standard form (must be preceded by the keyword DATE). TTU Windows Package. This Teradata Tools and Utilities (TTU) 16.20 package is the full collection of Teradata client tools for Windows. This includes SQL Assistant, load & unload utilities, database management, administration and drivers, and much more to be used to connect to your Teradata Express database instance (downloaded separately).

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’

BETWEEN for DATE types in Teradata, BETWEEN operator can be used for the date types which are in different formats. General syntax: SELECT column FROM table WHERE date_column  3 sql teradata filtering on date - database version Teradata 15.10.06.02 and provider version Teradata.Net 15.11.0.0 Jun 15 '18 2 python pandas find transacctions of the same amount Apr 17 '18 1 searching a word in the column pandas dataframe python Apr 20 '18

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.

How to align String on console output, 3065 | sql teradata filtering on date - database version Teradata 15.10.06.02 and provider version Teradata.Net 15.11.0.0; 9557 | How do I count the number  I'm having an issue copying from a filtered answerset in SQL Assistant. While testing code I might look for certain conditions like if a particular field is null, filter the results to show only those records then I'll want to copy certain identifying info (e.g. account numbers) so I can run a more detailed query on just those accounts.

15.10. Date and Time Functions and Operators, Returns timestamp as a UNIX timestamp. Note. The following SQL-standard functions do not use parenthesis: current_date; current_time  I don't know if this is based on Standard SQL, sql teradata filtering on date - database version Teradata 15.10.06.02 and provider version Teradata.Net 15.11.0.0.

interval, WHERE DB. By aaibinder, 30 Aug 2016 | Tagged: teradata date interval select statement | 2 comments We are extracting it from ORacle and loading to Teradata version 15.10. I'm new to teradta and when I'm running the following SQL:. Teradata Studio is a client based graphical interface used to perform database administration tasks on Teradata and Aster Databases, as well as copying data between Aster, Teradata, and Hadoop Database Systems. Teradata Studio is built on the Eclipse® Rich Client Platform (RCP), taking advantage of the RCP framework for building and deploying

16.10 - DATE Data Type, DATE with a DateForm of IntegerDate is a Teradata extension to the ANSI SQL:​2011 standard. Internal Representation of DATE. Internally, Teradata Database  Teradata SQL Assistant for Microsoft Windows is a query and information discovery tool used to retrieve data from Teradata Database or any ODBC-compliant database server. Users can then view, manipulate, or store the extracted data on their desktop PC. .NET Data Provider for Teradata can also be used to connect to Tera

Comments
  • I copied one of the cell values and it is 8/1/2017
  • @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
  • 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.
  • 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.
  • 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).