Jump to content

Convert various date formats into one


Adamhumbug
 Share

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 comment
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 comment
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.

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

 

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

 

Link to comment
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.

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.

 Share

×
×
  • 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.