Subtle differences in Teradata and SQL Server

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
)

 

Loading

2 thoughts on “Subtle differences in Teradata and SQL Server

    • Jens Vestergaard Post authorReply

      Indeed it Works, the Teradata equivalent SQL statement is: SELECT EXTRACT( YEAR FROM CURRENT_DATE ) * 100 + EXTRACT( MONTH FROM CURRENT_DATE )
      Funnu however, I discovered that YEAR(current_date) actually Works, despite the fact that it’s not documented

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.