Converting the Date into Various formats

General
Enthusiast

Converting the Date into Various formats

Hi All,

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


but i need the date in the below formats

YYYYMMDD HH:MM:SS

SELECT DATE (FORMAT 'YYYYMMDD||' ,'||"HH:MM:SS") (CHAR(17)) FROM DBC.TABLE;-----? 


2014SEPT07

SELECT DATE (FORMAT 'YYYYMMMDD') (CHAR(8)) FROM DBC.TABLE;-----? 

CURRENT DATE

?

CURRENT TIMESTAMP

?

CURRENT TIME-120(DAYS)

?

how can we achieve this????

3 REPLIES
Junior Contributor

Re: Converting the Date into Various formats

Hi Rakesh,

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.

Enthusiast

Re: Converting the Date into Various formats

Thanks Dieter ,nope i haven't worked on Oracle before.

CURRENT_TIMESTAMP(0)

why you have mentioned (0)over here?

Junior Contributor

Re: Converting the Date into Various formats

Hi Rakesh,

this specifies fractional seconds, (0) = no fraction up to (6) = microseconds