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

Question

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'.

Never, ever store dates as strings. You should fix your data model before proceeding. — , Dec 27 '13 at 16:10

Houari · Answer 1 · 2013-12-27T12:24:53.440

1

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

Here you can look to date formating: Source

edited Dec 27 '13 at 12:24

answered Dec 27 '13 at 12:18

Houari

5,326
3
31
54

How can i make this applicable for the entire column. Can you help me in this – user1087354 Dec 27 '13 at 12:36
What is the name of the column that contains the date string ? – Houari Dec 27 '13 at 12:51

score 1 · Answer 2 · edited May 23 '17 at 12:19

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.

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

2 Answers2