Help with a quick table query

[RIkStryker]

Hi All,

I have 'inherited' a project where the database / dates are a bit screwed up.

I have been handed a simple table with the column titles...

RecordId
InputDate
MySqlDate (which i added)

The id is the id, the InputDate unfortunately is a tinytext where the input format is for example...

20/08/2013

or

20/8/2013

I have added the MySqlDate as a date column to make everything easier for new entries / queries and all is good.

Now I need to convert / update the existing data from the InputDate column to the respective MySqlDate column.

i.e. from the tinytext to date - 0000-00-00

Got me stumped.

Any tips appreciated.

Rik

[Barand]

The magic incantation you require is STR_TO_DATE() function

 

UPDATE mytable SET newdate = STR_TO_DATE(olddate, '%d/%m/%Y')

[jazzman1]

Or,

UPDATE t1 SET MySqlDate = DATE_FORMAT(STR_TO_DATE(InputDate, '%d/%m/%Y'), '%Y-%m-%d');

It will be update the date column in format : YYYY-MM-DD (0000-00-00)

[Barand]

jazzman,

 

Why would you want to DATE_FORMAT() to Y-m-d on a date that is already in Y-m-d format?

mysql> SELECT STR_TO_DATE('20/08/2012', '%d/%m/%Y');
+---------------------------------------+
| STR_TO_DATE('20/08/2012', '%d/%m/%Y') |
+---------------------------------------+
| 2012-08-20                            |
+---------------------------------------+

[jazzman1]

 I've never thought about this special feature, master

[vinny42]

 

is for example...
20/08/2013
or
20/8/2013

 

If both (or more?) formats are used then be carefull with Barand's solution, you *MUST* check to see what the exact format is before you convert.

If the format is m/d/y then you might interpret 12/30/13 as the 12thday of the 30th month, and MySQL will spew the infamous crap of "0000-00-00".

Some sort of regexp is in order, and you should probably configure MySQL to use "traditional" mode so it eill throw errors instead of silently useing 0000-00-00.

[RIkStryker]

Whoah, thanks for the answers - gonna give these a proper read thru and I'll let you know my results.

 

Pete

[RIkStryker]

If both (or more?) formats are used then be carefull with Barand's solution, you *MUST* check to see what the exact format is before you convert.

If the format is m/d/y then you might interpret 12/30/13 as the 12thday of the 30th month, and MySQL will spew the infamous crap of "0000-00-00".

Some sort of regexp is in order, and you should probably configure MySQL to use "traditional" mode so it eill throw errors instead of silently useing 0000-00-00.

 

Thanks, and yes that has come up. Barand's solution almost does the job. I know the format is the issue so I now need to check the format.

 

i.e.

 

1. Check first if column InputDate has a value and value is not default (00/00/0000), if so, proceed...

2. Grab parts of the InputDate, slicing by slashes and ensuring padding is cool.

 

Thanks again for the input, I'll update with any findings.

 

Pete

[Barand]

As you can see below, it is pretty lenient when it comes to format

mysql> SELECT strdate, STR_TO_DATE(strdate, '%d/%m/%Y') FROM datetest;
+------------+----------------------------------+
| strdate    | STR_TO_DATE(strdate, '%d/%m/%Y') |
+------------+----------------------------------+
| 20/08/2012 | 2012-08-20                       |
| 20/8/2012  | 2012-08-20                       |
| 2/8/12     | 2012-08-02                       |
+------------+----------------------------------+

Find the ones that won't convert with

SELECT * FROM mytable
WHERE STR_TO_DATE(olddate, '%d/%m/%Y')) IS NULL 
OR STR_TO_DATE(olddate, '%d/%m/%Y')) = '0000-00-00'
OR STR_TO_DATE(olddate, '%d/%m/%Y')) = '1970-01-01'

[RIkStryker]

Thanks all, worked like a charm (and learning every day).

 

Pete