CODEWITHSUNDEEP
sqlteradata
Nidhin_toms
Nidhin_toms

Reputation: 737

check for dates syntax - teradata SQL

I am trying to check for dates but after running the query below, it displays no result. Could someone recommend me the correct syntax?

SELECT TOP 10 * FROM MY_DATABASE.AGREEMENT
WHERE end_dt=12/31/9999

Upvotes: 0

Views: 3331

Answers (3)

dnoeth
dnoeth

Reputation: 60482

12/31/9999 might look like a date for you but for the database it's a calculation:

12 divided by 31 divided by 9999 and because this involves INTEGER division this results in an INTEGER 0

So finally you compare a DATE to an INT and this results in typecasting the DATE to a INT.

The only reliable way to write a date literal in Teradata is DATE followed by a string with a YYYY-MM-DD format:

DATE '9999-12-31'

Similar for TIME '12:34:56.1' and TIMESTAMP '2014-08-20 12:34:56.1'

Upvotes: 3

Andrew
Andrew

Reputation: 8758

Is it a date column? Then try where end_dt = '9999-12-31'.

Upvotes: 1

TonT
TonT

Reputation: 36

The question you ask is not very clear. The date you specify is language dependent. Try SELECT TOP 10 * FROM MY_DATABASE.AGREEMENT WHERE end_dt='99991231'

Upvotes: 0

Related Questions