Conversion of timestamp to date type mmddyy fails

to_date(cast(timestamp_column as varchar), 'DDMMYY')

So breaking this down, start at the inner most function.

cast(timestamp_column as varchar)

This will convert the timestamp to varchar and be something like this 2022-11-09 15:22:48.594634+00

So then you have to_date() with the mask of DDMMYY. This means you are telling the to_date function that the format of the first parameter will be formatted like 091122 when it isn't at all. I'm guessing the output was something like 1990-10-22.

How it is stored versus how it is displayed are two different things. It is a timestamp so don't worry about how it is stored. It isn't a string. So to display the timestamp in the format you want, use to_char. It means you are taking the timestamp and formatting with the mask you are providing.

to_char(timestamp_column, 'DDMMYY')

For example:

select to_char(now(), 'DDMMYY');
 to_char 
---------
 091122

Hi akc_adi,

Have you tried the following?

select to_char(timestamp_column,'DDMMYY')

If the timestamp_column is stored as varchar, then you have to cast to timestamp before:

select to_char(cast('2020-01-31 01:14:00.000' as timestamp),'DDMMYY')

Thanks,