CODEWITHSUNDEEP
sqlteradata
Doug Fir
Doug Fir

Reputation: 21292

Date Formats Not Working

I'm using Teradata as my SQL client and have been playing around with date format formula.

Many seem to not work.

Here's a sample query that does work:

EVENT_GMT_TIMESTAMP(FORMAT 'yyyy-mm')(char(7)) AS YEAR_MONTH,

But I found some ways that I thought would be easier to remember yet none seem to work:

DATE_FORMAT(EVENT_GMT_TIMESTAMP,'%M-%Y')

Gives syntax error "EVENT_GMT_TIMESTAMP does not match defined type name"

Then tried (My favourite potential solution):

EXTRACT(YEAR_MONTH FROM EVENT_GMT_TIMESTAMP) AS YEAR_MONTH,

Gives syntax error "Expected something like DAY keyword or HOUR keyword or MINUTE keyword between "(" and YEAR_MONTH.

Then tried, just to see if it works:

CONCAT(EXTRACT(YEAR FROM EVENT_GMT_TIMESTAMP),  EXTRACT(MONTH FROM EVENT_GMT_TIMESTAMP))  AS YEAR_MONTH,

Gives syntax error "expected soemthing between "(" and EXTRACT keyword.

I read about these functions online and don;t understand why they are failing. Am I missing something?

Upvotes: 0

Views: 550

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

To my knowledge and testing the following are not a native functions or ODBC extension as of Teradata 14.0: 

DATE_FORMAT(EVENT_GMT_TIMESTAMP,'%M-%Y')
CONCAT(EXTRACT(YEAR FROM EVENT_GMT_TIMESTAMP),  EXTRACT(MONTH FROM EVENT_GMT_TIMESTAMP))  AS YEAR_MONTH,

'YEAR_MONTH' is not a valid INTERVAL type for use with the EXTRACT function here:

EXTRACT(YEAR_MONTH FROM EVENT_GMT_TIMESTAMP) AS YEAR_MONTH,

Consider the following instead:

EXTRACT(YEAR FROM EVENT_GMT_TIMESTAMP) || '-' || EXTRACT(MONTH FROM EVENT_GMT_TIMESTAMPE) AS YEAR_MONTH,

Although your first solution is probably the most flexible in terms of formatting options available within the database.

Upvotes: 1

Related Questions