CODEWITHSUNDEEP
sqlpostgresqldatetype-conversion
user1087354
user1087354

Reputation: 1

Format a date stored as "text" to the pattern "mm-dd"

I want to change the text data type to "mm-dd" while extracting.

The column holds a date and data type is text.

Original data: 'Sat Aug 31 2013'. I want this as '8-31'.

Upvotes: 0

Views: 358

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

As @a_horse commented, the proper fix would be to convert your column to date. While stuck with your unfortunate design ...

Sat in the string Sat Aug 31 2013 is redundant. Actually, to_date() ignores it in this pattern. General Validity of 3-letter abbreviation is checked, then it's ignored. Could even be the wrong day. Faster to ignore it a priori. I quote the manual:

In to_date, to_number, and to_timestamp, double-quoted strings skip the number of input characters contained in the string, e.g. "XX" skips two input characters.

Also, to skip leading zeros (and whitespace) in the output like your example ('8-31') suggests, use the FM modifier.

SELECT to_char(to_date(date_col, '"XXX "Mon DD YYYY'),'FMMM-DD')
FROM   tbl

-> SQLfiddle demo.

Upvotes: 1

Houari
Houari

Reputation: 5621

select to_char(to_date(date_string_column_name, 'DY Mon DD YYYY'),'MM-YY')

Here you can look to date formating: Source

Upvotes: 1

Related Questions