Concatenate in Teradata 15.10.01.01

General
Enthusiast

Concatenate in Teradata 15.10.01.01

Hello,

I am pretty new to SQL and trying to figure out a solution to following error.

I am running this query but it gives me error that [expected something between 'Concatenate' and '(' ] 

 

CAST( CONCATINATE (SUBSTRING(CAST(MLR.CAL_YR_MO_NUM AS VARCHAR(6)),5,2),'/', '01','/',SUBSTRING(CAST(MLR.CAL_YR_MO_NUM AS VARCHAR(6)),1,4)) AS DATE FORMAT 'MM/DD/YYYY' ) AS "SVC_DT"

 

The query runs fine when I inactivate this line. Does anyone know a solution to this?

 

Many thanks,


Accepted Solutions
Junior Contributor

Re: Concatenate in Teradata 15.10.01.01

All three variations I posted will work, you can simply concat '01' or apply a Format without the day, it defaults to the first of the month then.

1 ACCEPTED SOLUTION
12 REPLIES
Enthusiast

Re: Concatenate in Teradata 15.10.01.01

SyntaxEditor Code Snippet

select cast (('2011'||'-'||'04'||'-'||'20')  as date format 'yyyy-mm-dd')

change the format accordingly 

Enthusiast

Re: Concatenate in Teradata 15.10.01.01

SyntaxEditor Code Snippet

select cast (('2011'||'-'||'04'||'-'||'20')  as date format 'yyyy-mm-dd')

change the format accordingly and concatenate spelling wrong migt be issue too :)

Enthusiast

Re: Concatenate in Teradata 15.10.01.01

there are many years and months. so i can not use any single year. The date field is as 200,401 and that is why I am using cast and substring.

About concatenate, I actually have Concat. I also tried Concatenate- corrected the spelling- thanks for noticing:) but still doesn't work.

thank you,

Junior Contributor

Re: Concatenate in Teradata 15.10.01.01

You got two problems with your SQL:

  1. there's no CONCAT in Standard SQL/Teradata. It is part of ODBC SQL and older ODBC-drivers might translate it into proper "||" syntax: SELECT col1||col2
  2. Similar for your SUBSTRING, it's ODBC-syntax, which is a mixture between SUBSTR(col, x, n) and SUBSTRING(col FROM x FOR n), the latter is Standard SQL, but both are valid in Teradata.

This will work:

Cast( Substr(Cast(MLR.CAL_YR_MO_NUM AS VARCHAR(6)),5,2) || '/' || '01' || '/' || Substr(Cast(MLR.CAL_YR_MO_NUM AS VARCHAR(6)),1,4) AS DATE Format 'MM/DD/YYYY' ) AS "SVC_DT"

Simplified to:
Cast(Trim(MLR.CAL_YR_MO_NUM) AS DATE Format 'yyyymm')

 

But you don't need typecasts to/from string (which are expensive):

Cast((MLR.CAL_YR_MO_NUM - 190000) * 100 + 1 AS DATE)

This is based on how a date is internally stored as INT:

(year - 1900) * 10000 + (month * 100) + day 

 

Teradata Employee

Re: Concatenate in Teradata 15.10.01.01

Concatenate is not a function in Teradata SQQL.  Concatenation is performed with the "||" operator specified between the string expressions to concatenate.

Enthusiast

Re: Concatenate in Teradata 15.10.01.01

thank you Dnoeth.

How can I put Date '01' as static and pull Month and year from the table?

Junior Contributor

Re: Concatenate in Teradata 15.10.01.01

All three variations I posted will work, you can simply concat '01' or apply a Format without the day, it defaults to the first of the month then.

Enthusiast

Re: Concatenate in Teradata 15.10.01.01

Thank you so very much!

Enthusiast

Re: Concatenate in Teradata 15.10.01.01

I thought it was returning the result but now gor another error as - invalid date supplied.

How to do concat '01' in the moddle to get the date in MM/DD/YYYY format? in the column there is only year and month.

sorry if it is a silly question.