Jump to content

Convert various date formats into one

Recommended Posts

I have a table that has dates stored in various formats (2021-03-12, 12 Mar 2021).  I also have a time stored as a string (07:00)

I am wanting to convert the dates in their various formats into one that can be concatonated with the time so that i can find any date/times that are 36 hours old or more.

I am pretty stuck with this after trying many different ways and would really appreciate a helping hand on this.

Thanks in advance.

Link to post
Share on other sites

Are you saying you have

  • a single (varchar) column with dates in different formats, or
  • multiple date columns in the table?

A sample of the data would help.

Easiest way would be to store the time and the date in a single DATETIME column, then search for "WHERE mydatetime < NOW() - INTERVAL 36 HOUR".

You need to convert dates that are not in yyyy-mm-dd format to that format.

Link to post
Share on other sites

I have one varchar column.

Unfortunately i am unable to change the table structure and have to deal with one varchar for date and one varchar for time.

An example of the data is below

Screenshot 2021-03-23 at 13.11.20.png

Link to post
Share on other sites

Don't allow users to enter dates in any format - use a datepicker or HTML5 date input to enforce uniformity of input, and always store in yyyy-mm-dd format.

Luckily you only appear to have only three formats (Y-m-d / d M Y / d-m-Y) so all is not lost.


mysql> select * from date_example;
| id | date        | time     |
|  1 | 2005-05-21  | 14:00:00 |
|  2 | 22 Jun 2006 | 13:00:00 |
|  3 | 01-11-2009  | 12:00:00 |
|  4 | 22-03-2021  | 00:05:00 |
|  5 | 28 Mar 2021 | 08:00:00 |


        SELECT id
             , date
             , CASE WHEN str_to_date(date, '%d %b %Y') IS NOT NULL        -- eg 01 Jan 2021
                            THEN str_to_date(date, '%d %b %Y')
                    WHEN str_to_date(date, '%d-%m-%Y') IS NOT NULL        -- eg 10-01-2021
                            THEN str_to_date(date, '%d-%m-%Y')
                    ELSE date 
                    END as newdate
             , time
        FROM date_example
     ) converted
WHERE concat(newdate, ' ', time) < NOW() - INTERVAL 36 HOUR;


| id | date        | newdate    | time     |
|  1 | 2005-05-21  | 2005-05-21 | 14:00:00 |
|  2 | 22 Jun 2006 | 2006-06-22 | 13:00:00 |
|  3 | 01-11-2009  | 2009-11-01 | 12:00:00 |
|  4 | 22-03-2021  | 2021-03-22 | 00:05:00 |


Link to post
Share on other sites

This is really useful, thanks so much.

As always, one more issue.

I am getting an error in the where clause.

My where clause is

WHERE concat(newdate, ' ', extractvalue(pr_customfields,'/CUSTOMFIELDS/CF[@CFN="TestedTime"]/@CFV')) < NOW() - INTERVAL 36 HOUR;

I am getting the error - Unknown column 'pr_customfields' in 'where clause'

I think the issue may have something to do with the coma in the concat but i have replaces all of your istances of "time" with the above and "date" with something very similar.

The data is stored in xml in the database in the following format hence the method for extraction (maybe there is another way)

<?xml version="1.0"?>
<CF CFN="TestedTime" CFV="" CFUID="CF5fb7e3b8ca7d8"/>


Link to post
Share on other sites

I think i have fixed this.

Added "as newtime" after the ",time" in your example and that seems to be working when i use "newtime" in the where clause.


Thanks so much as every for your continued support!!

Link to post
Share on other sites
18 minutes ago, Cloud_Geek said:

I suggest to use date_format() function for this purpose. 

It would be nice to see your solution using date_format().

Are you talking about PHP's date_format function or SQL's function?

Link to post
Share on other sites

I can't see a single mention of date_format() in that link. It advocates the use of str_to_date(), which is what my solution used.

That still leaves the question of your solution using date_format().

Link to post
Share on other sites

Join the conversation

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

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.