I have tried to convert the date in various formats
my present date column in the below format
YYYY-MM-DD --> but i want the date to be displayed in MMM-YY-DD.
SELECT DATE (FORMAT 'MMM''YY') (CHAR(6)) FROM DBC.TABLE; --->SUCCEDDED
SELECT DATE (FORMAT 'YYYYMMDD||' ,'||"HH:MM:SS") (CHAR(17)) FROM DBC.TABLE;-----?
a DATE is a date and not a TIMESTAMP, did you work with Oracke before?
SELECT CURRENT_DATE, CURRENT_TIMESTAMP(0), CURRENT_TIMESTAMP - INTERVAL '120' DAY
If you're on TD14 you might also use Teradata's implementation of Oracle's TO_CHAR.