Bart Schuijt Bart Schuijt - 6 months ago 14
SQL Question

Converting date to character format in SQL

I'm trying to convert a date format stored m/d/yyyy to a 'yyyymmdd' character format, without the need to cast the date column every time I want to use the column in my query.

Currently I'm casting and formatting dates as chars:

((cast(cast(invitation_date as CHAR(8)) as date format 'YYYYMMDD'))
.

Is there a way to convert the data column once and call the converted character value later in the query?

I'm using Teradata in Aqua Data Studio 13.0.3.

Answer

If source is a DECIMAL yyyymmdd you can do

CAST(invitation_date - 19000000 AS DATE) AS newcol

Teradata allows an alias to be used in any place, so you can simply do

WHERE newcol > DATE

Of course best case would be to change those columns to DATE during load.