All Forums Database
SQLfan123 2 posts Joined 08/16
28 Aug 2016
Filtering results of concatenation

Hi Everyone!
I'm having trouble with filtering concatenation results in Teradata. I'm using the following syntax "EXTRACT(YEAR from saledate)||EXTRACT(MONTH from saledate) AS date_num" to combine the year number with the month number for a variety of dates. This works great and the results are like the following "2005 4", "2008 9" etc.
However, when I try to select only a certain date pairing in a WHERE clause (WHERE date_num = '2005 4') for example, Teradata tells me no data is available. Why is it not recognizing the '2005 4'? Am I doing something wrong, or is there a workaround?
Thanks for any help!

Fred 1096 posts Joined 08/04
28 Aug 2016

It's the extra spaces introduced by implicit typecast from INTEGER result of extract. The default is FORMAT -(10)9 so the year, for example is 2005 with 5 leading spaces.
Explicitly CAST(EXTRACT(YEAR from saledate) as VARCHAR(4)), which trims the spaces, or rely on implicit cast but explicitly TRIM the result. You may want to explicitly concatenate a single space between year and month.
Or use FORMAT with CAST to explicitly control the result, e.g. CAST(CAST(EXTRACT(YEAR from saledate) AS FORMAT '9999') as VARCHAR(4)). This method would also allow you to have months less than 10 appear as two digits with a leading zero.

SQLfan123 2 posts Joined 08/16
29 Aug 2016

Thanks, Fred! I used this approach: "CAST(EXTRACT(YEAR from saledate) as VARCHAR(4))||' '||CAST(EXTRACT(MONTH from saledate) as VARCHAR(4)) AS date_num" and it worked perfectly.

You must sign in to leave a comment.