Jump to content

Recommended Posts

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

Edited by RIkStryker
Link to comment
https://forums.phpfreaks.com/topic/282799-help-with-a-quick-table-query/
Share on other sites

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                            |
+---------------------------------------+

 


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.

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

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'
Edited by Barand
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.