regular expression to see if mysql is a date

Well, not sure if this is the best (as it would allow for February 31st, or other months that don't have 31 days) but an easy one to do would be just to match each set of numbers:

^([1-2][0-9]{3,3})-(0?[1-9]|1[0-2])-(0?[1-9]|[1-2][1-9]|3[0-1])\s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9])$

Select allOpen in new window

This would match years 1000-2999, and then months 1-12, days 1-31, hours 0-24 and minutes/seconds 0-59 basically expecting a string to be in: YYYY-MM-DD HH:MM:SS format.

@kaufmed, I did a bit of research, and as far as I could tell, MySQL does not support native escaped chars, such as \\d, with these 7 exceptions: b t n v f r . But, if I'm understanding this correctly, they must be enclosed within special brackets, [. .] like so:

[[.\n\r.]] -- This matches any newline

Select allOpen in new window

It does, however, natively support escaping, such as \\+. I'm not sure on if the special cases above need double backslashing...

A simpler regex (with the same limitation as @matthew pointed out with his regex) might be:

^([0-9]{2,4}-){3} *([[0-9]{1,2}:){2,3}$

I've made some more complex regex before, that would account for any separator in any order, validating any date formatted in any fashion, but that was using PCRE, so...there you go. :-)

@bigdogdman

Your pattern requires a hyphen after the day and a colon after the seconds.

Oh, my mistake; good catch, thanks kaufmed. :-)

^([0-9-]{2,5}){3} *([[0-9:]{1,3}){2,3}$

Select allOpen in new window

This of course makes it less strict; let me see if I can refactor it:

^([0-9]{2,4}-){2}[0-9] *([[0-9]{1,2}:){1,2}[0-9]$

Select allOpen in new window

okay
 select column from table when/where column REGEXP 'somedatepattern'

is this the proper select

According to the documentation, it should be. Use WHERE not WHEN, though.

Personally, I like to use 'RLIKE' simply because there's less chance I misspell it:

SELECT column FROM table WHERE column RLIKE '^([0-9]{2,4}-){2}[0-9] *([[0-9]{1,2}:){1,2}[0-9]$';

Select allOpen in new window

I would have a look at the built-in date functions.
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date

Select column from table where str_to_date(column, '@Y-@m-@d @h:@i:@s') is NOT NULL;

I think you're making this a lot more complicated than it needs to be.  You know the names of the columns because you can execute SHOW CREATE TABLE.  You probably know what columns contain DATETIME values, but are miscaptioned VARCHAR.  So the really simple process would go like this.

1. ALTER TABLE to add the new DATETIME column
2. SELECT the key and the miscaptioned column, with each row
A. Use PHP strtotime() to make a UNIX timestamp and date('c') to make the ISO-8601 date string
B. UPDATE the row to set the value in the new DATETIME column
C. Lather, rinse, repeat.

At the end of the process you can check the success by looking for DATETIME values of January 1, 1970 UTC.  These are the rows where the date conversion failed.  You can correct those manually by examining the information from the miscaptioned column.  PHP strtotime() is pretty forgiving (much moreso than REGEX) so there will probably be very few rows where the process fails.

Once you're satisfied with the results, you can probably just rename the columns and your scripts will continue to work correctly, but it would still be a good idea to have the appropriate backups and test cases prepared ahead of time.

Ray: I wrote that I want to do this without php because php would be more difficult for me to test.  Mysql, I just copy paste into mysql workbench


SELECT column FROM table WHERE column RLIKE '^([0-9]{2,4}-){2}[0-9] *([[0-9]{1,2}:){1,2}[0-9]$';

NO results

Select column from table where str_to_date(column, '@Y-@m-@d @h:@i:@s') is NOT NULL
no results

I did not know the exact query syntax to try the other regexes

There is a saying among experienced programmers that goes like this: "I had a problem so I used a regular expression.  Now I have two problems."  The reason I suggested getting PHP strtotime() involved in the process is because it will reduce the number of problems, not increase them.  It's your data base, so you're free to choose any solution that works for you.

Please see http://www.laprbass.com/RAY_temp_rgb192.php

You will see that strtotime() is so much more forgiving than REGEX!

Test data is at lines 10-20.  ALTER TABLE is at line 98.  SELECT/UPDATE is at line 138.  Always back up your tables before you try something like this.

<?php // RAY_temp_rgb192.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Programming/Languages/Regular_Expressions/Q_28312994.html


// CREATE AN ARRAY OF TEST DATA
$test_dates_arrays = array
( array( "vdate" => "2013-12-04 00:44:52" )
, array( "vdate" => "2013-12-04 00:45:53" )
, array( "vdate" => "2013-12-04 00:46:58" )
, array( "vdate" => "Yesterday" )
, array( "vdate" => "Gooseball" )
, array( "vdate" => "Tomorrow"  )
, array( "vdate" => "Now"  )
, array( "vdate" => " 2013-12-4     00:44:52"  )
, array( "vdate" => " December 17, 2:38pm"  )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, vdate VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
foreach ($test_dates_arrays as $row)
{
    $safe_dt  = $mysqli->real_escape_string($row['vdate']);
    $sql = "INSERT INTO my_table ( vdate ) VALUES ( '$safe_dt' )";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_dt</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// ALTER THE TABLE TO ADD THE DATETIME COLUMN
$sql = "ALTER TABLE my_table ADD COLUMN ddate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// VISUALIZE THE TABLE STATUS AFTER "ALTER TABLE"
$sql = "SHOW CREATE TABLE my_table";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// GET THE ROWS THAT HAVE EMPTY ddate VALUES
$sql = "SELECT * FROM my_table WHERE ddate = '0000-00-00 00:00:00' ORDER BY id";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO UPDATE THE COLUMNS
while ($row = $res->fetch_object())
{
    // IF THE TIMESTAMP IS OK
    $tst = strtotime($row->vdate);
    if ($tst)
    {
        $new = date('c', $tst);
        $uql = "UPDATE my_table SET ddate = '$new' WHERE id = $row->id LIMIT 1";
        if (!$upd = $mysqli->query($uql))
        {
            $err
            = 'QUERY FAILURE:'
            . ' ERRNO: '
            . $mysqli->errno
            . ' ERROR: '
            . $mysqli->error
            . ' QUERY: '
            . $uql
            ;
            trigger_error($err, E_USER_ERROR);
        }
    }
}
echo PHP_EOL;


// GET ALL THE ROWS TO SHOW WHAT WE HAVE DONE
$sql = "SELECT * FROM my_table ORDER BY id";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;

Select allOpen in new window

I agree with you: Ray is always right, but i asked for a mysql query because I didnt want to use php because pdo/mysqli, select, update would take me hours.

I tested bigdogdman's query of kaufmed regex in seconds.

I am not sure if Ray has the correct answer to this question:
because I wrote

what is the regular expression for mysql (not using php) to see if varchar is written like a date format

Ray has the correct answer that will solve my project which Ray has been advising me for months.

So this is the followup.

https://www.experts-exchange.com/questions/28319524/use-php-and-mysql-to-update-varchar-to-mysqldatetime-value-or-NULL.html