Convert various date formats into one

[Adamhumbug]

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.

[Barand]

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.

[Adamhumbug]

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

[Barand]

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.

TEST DATA

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

QUERY

SELECT *
FROM (
        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;

RESULTS

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

 

[Adamhumbug]

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"?>
<CUSTOMFIELDS>
<CF CFN="TestedTime" CFV="" CFUID="CF5fb7e3b8ca7d8"/>
</CUSTOMFIELDS>

 

[Adamhumbug]

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

[Cloud_Geek]

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

[Barand]

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?

[Cloud_Geek]

I was talking about php. In MySQL, to do so , you should follow the solution mentioned here. 

[Barand]

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().