CODEWITHSUNDEEP
amazon-redshiftto-char
MariaS
MariaS

Reputation: 345

RedShift to_char format not giving desired results

I need to convert my timestamps to the following format YYYY-MM-DDTHH24:MI:SS.0. Please note the 'T' in the middle. For example 2014-09-04T13:05:10.0.

I tried the following:

select to_char(timestamp,'YYYY-MM-DDTHH24:MI:SS.0') from table;

However, RedShift is interpreting the 'TH' as an ordinal number suffix (http://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_formating.html). Therefore, I'm getting results like 2014-03-23RDH24:26:36.0.

Therefore, I added a space after the 'T' and tried trimming the space out.

select trim(' ' FROM to_char(timestamp,'YYYY-MM-DDT HH24:MI:SS.0')) from table;

However, the trim did not actually remove the space. My results were like 2014-03-23T 07:26:36.0.

I also tried escaping by using the backslash, but that didn't work either.

How do I achieve the desired format without spaces?

Upvotes: 1

Views: 3034

Answers (2)

chris
chris

Reputation: 31

upper(to_char(GetDate(),'YYYY-MM-DDtHH24:MI:SS.0'))

works too.

Upvotes: 3

MariaS
MariaS

Reputation: 345

This ended up working

select concat(to_char(timestamp,'YYYY-MM-DDT'), to_char(timestamp,'HH24:MI:SS.0')) from table;

Upvotes: 1

Related Questions