I have a BRTH_DT [CHAR(8)] field in a table I am trying to query using Teradata SQL Assistant. The values store are as follows; 19860101, 19701215, etc. I am trying to convert this field so my output is a DATE. Then subtract that date to TODAYS date to see how old.
Either CAST(BRTH_DTE as DATE FORMAT 'YYYYMMDD')
should work for the conversion.
If you subtract two dates, the difference by default will be the (integer) number of days between them.
For age in years, perhaps you want
CAST(MONTHS_BETWEEN(CURRENT_DATE, CAST(BRTH_DTE as DATE FORMAT 'YYYYMMDD')) AS INTEGER)/12
CAST(BRTH_DTE as DATE FORMAT 'YYYYMMDD') returns a  Invalid Date error
TO_DATE(BRTH_DTE,'YYYYMMDD') returns a  YYYY value must be four digits and in the rang 1-9999 error.
will nulls or blanks in this column have an effect?
If BRTH_DTE contains a NULL, both CAST and TO_DATE should return NULL without an error.
But spaces would be a problem, since the data would not be in the indicated format.
TRYCAST(SUBSTRING(BRTH_DTE FROM 1 FOR 4)||'-'||SUBSTRING(BRTH_DTE FROM 5 FOR 2)||'-'||SUBSTRING(BRTH_DTE FROM 7 FOR 2) AS DATE)
would return NULL for anything that can't be converted to date. (TRYCAST doesn't allow you to specify a format, it expects dates to be YYYY-MM-DD.)
Or if the problem is only specific values (e.g. blanks) then you might use a CASE statement to convert those to NULL and avoid the error.
CAST(CASE WHEN BRTH_DTE = '' THEN NULL ELSE BRTH_DTE END as DATE FORMAT 'YYYYMMDD')