Convert CHAR(8) to DATE

Analytics
Highlighted
Enthusiast

Convert CHAR(8) to DATE

Hello,

 

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. 

3 REPLIES 3
Teradata Employee

Re: Convert CHAR(8) to DATE

Either CAST(BRTH_DTE as DATE FORMAT 'YYYYMMDD')

or TO_DATE(BRTH_DTE,'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
Enthusiast

Re: Convert CHAR(8) to DATE

CAST(BRTH_DTE as DATE FORMAT 'YYYYMMDD') returns a [2665] Invalid Date error

TO_DATE(BRTH_DTE,'YYYYMMDD') returns a [9134] YYYY value must be four digits and in the rang 1-9999 error.

 

will nulls or blanks in this column have an effect?

Teradata Employee

Re: Convert CHAR(8) to DATE

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.

 

Something like

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')