Imagine the hair pulling experience, when you port knowledge from one RDBMS to another, and it just doesn’t work. I had one such experience today, when I had to generate a certain filter for at Query. I wanted to combine the year and month value, to filter on the unique month key which consisted of the concatenation of year and month (YYYYMM).
In SQL Server, you could do something like this:
SELECT
CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4))+
IIF(DATEPART(MONTH,GETDATE())< 10
,‘0’+CAST(DATEPART(MONTH,GETDATE())AS VARCHAR(2))
,CAST(DATEPART(MONTH,GETDATE())AS VARCHAR(2))
)
The result of the above query, would be ‘201402’. But, in Teradata, that just results in 2016 (for the current month of February), even when doing the explicit cast mind you.
Turns out, that CONCAT(…) is your friend – Otherwise you just end up with a number, even when explicitly casting to VARCHAR(x)
SELECT CONCAT( CAST( EXTRACT( YEARFROMCURRENT_DATE ) AS VARCHAR(4) ), CASE WHEN EXTRACT( MONTHFROMCURRENT_DATE ) < 10THEN CONCAT( '0' , CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) ) ) ELSE CAST( EXTRACT( MONTHFROMCURRENT_DATE ) AS VARCHAR(2) ) END )