CODEWITHSUNDEEP
sqldateteradatavarchar
Grigory P
Grigory P

Reputation: 191

Teradata: how to convert varchar value (format 'dd.mm.yyyy') to date (format 'yyyy-mm-dd' )?

how to convert varchar value, containing date in dd.mm.yyyy format, into date format 'yyyy-mm-dd' in WHERE clause in Teradata ? Need to code something like this:

SELECT * from TABLE
WHERE <some operations with VARCHAR variable containing date> between '2015-06-01' and '2017-12-31'

Upvotes: 1

Views: 12166

Answers (1)

dnoeth
dnoeth

Reputation: 60482

You need to apply a format during the cast, either Teradata style:

WHERE CAST(str AS DATE FORMAT 'dd.mm.yyyy') 
BETWEEN DATE '2015-06-01' AND DATE '2017-12-31'

or Oracle style:

WHERE TO_DATE(str, 'dd.mm.yyyy') 
BETWEEN DATE '2015-06-01' AND DATE '2017-12-31'

Btw, I added DATE in front of the string, it's the recommended (and always reliable) way to write a date literal.

Upvotes: 4

Related Questions