Formatting Dates Stored as Strings

Question

I am working with a string column that holds date values (I didn't design it, but I do have to work with it). There are about 2500 records.

Most of the date values are entered in MM/DD/YYYY format (with preceding '0's), but there are some rogue records that are in MM/DD/YY format, or others that don't have preceding '0's.

Example of source data:

05/20/2012
05/20/2012
5/29/2011
5/6/2015
5/1/10

I need to return a distinct list of values, all formatted the same way (MM/DD/YYYY), in order.

Given the lack of consistency in how these values were entered, I'm not sure how to accomplish the goal?

If you are going to standardize the dates, I would recommend the ISO standard format of YYYY-MM-DD. — Gordon Linoff
– Gordon Linoff, Commented Jan 29, 2016 at 16:29
In this case 5/1/10 no one can say which value is a year, a month or a day. I guess that there also could be dates like 10/11/12 in this table - it's guessing game, you need some additional criteria to decide which format has to be used in this case. Also a date like 10/12/2015 - please tell mi which value is a month and which one is a day ? — krokodilko
– krokodilko, Commented Jan 29, 2016 at 16:57
@kordirko - I feel comfortable assuming a value such as 5/1/10 is intended to mean May 1, 2010. This is a highly localized database, and I see my colleagues use that format often. — Brian Brock
– Brian Brock, Commented Jan 29, 2016 at 16:59

krokodilko · Accepted Answer · 2016-01-29 17:12:07Z

If dates in the column are in only 2 possible formats:
x/xx/xx -> mm/dd/yy
xx/xx/xxxx -> mm/dd/yyyy

then use simple to_date( column_name, 'mm/dd/yy' ) to convert strings to dates,
and then to_char( value, 'mm/dd/yyyy' ) to convert dates back to required format.

Simple:

to_char( to_date( column_name, 'mm/dd/yy' ), 'mm/dd/yyyy' )

You can use only one conversion format mm/dd/yy in to_date function, because of conversion rules used by Oracle and descibed here (under topic "String-to-Date Conversion Rules" ):
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

In short - if a format model mm/dd/yy doesn't match to string like 10/12/2014, then Oracle automatically tries alternative format model: mm/dd/yyyy

algor · Accepted Answer · 2016-01-29 16:59:10Z

Well, it's not given what DB is used in this particular case. Let's assume that this is Oracle DB and all dates start with Month, and desired result is string, and 5/1/10 is not 1st of May 1910. You can do this:

select to_char(to_date('5/1/10', 'MM/DD/YY'), 'MM/DD/YYYY') from dual;
select to_char(to_date('05/20/2012', 'MM/DD/YY'),'MM/DD/YYYY') from dual;
select to_char(to_date('5/29/2011', 'MM/DD/YY'), 'MM/DD/YYYY') from dual;
select to_char(to_date('5/6/2015', 'MM/DD/YY'),'MM/DD/YYYY') from dual;

you'll get

Collectives™ on Stack Overflow

Formatting Dates Stored as Strings

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related