Explain CAST(a.dlv_yr || '-' || CASE WHEN a.dlv_mth < 10 THEN '0' ELSE '' END || TRIM

Database
Enthusiast

Explain CAST(a.dlv_yr || '-' || CASE WHEN a.dlv_mth < 10 THEN '0' ELSE '' END || TRIM

Can someone please explain  

CAST(a.dlv_yr || '-' || CASE WHEN a.dlv_mth < 10 THEN '0' ELSE '' END || TRIM(a.dlv_mth) || '-' || '01' AS DATE) AS mth,

 

2 REPLIES
Junior Contributor

Re: Explain CAST(a.dlv_yr || '-' || CASE WHEN a.dlv_mth &lt; 10 THEN '0' ELSE '' END || TRIM

This calculates the first of month for a given year/month by converting year/month to a string, adding a leading zero to a single digit month, and finally casting to a date.

 

A more effficient version based on Teradata's internal date format avoiding double type casts (numeric -> string -> date):

Cast((dlv_yr -1900) * 10000 + dlv_mth * 100 + 1 AS DATE) AS mth

 

Highlighted
Teradata Employee

Re: Explain CAST(a.dlv_yr || '-' || CASE WHEN a.dlv_mth &lt; 10 THEN '0' ELSE '' END || TRIM

Apparently table "a" contains character columns dlv_mth and dlv_yr, and this formats them as a date, the first day of the month.

Spaces are removed from a.dlv_mth by the trim() function; then for those single-digit months a '0' is prepended to the character, so that the month is always represented as 2 digits.  A.dlv_yr is assumed to be 4 digits..  The result of the concatenations (||) is a string of the form yyyy-mm-dd (dd has the constant value '01'), which can then be cast as a date.  This date is apparently given the name "mth" in a select statement.