PDO::PARAM for dates?

Does some PDO::PARAM_??? exist which can be used for dates or timestamps?

Sample code:

$sql = "UPDATE my_table SET current_date = :date WHERE id = 43";
$statement = $pdo->prepare ($sql);
$statement->bindValue (":date", strtotime (date ("Y-m-d H:i:s")), PDO::PARAM_STR);
$statement->execute ();

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

When writing a date in an SQL query, you are writing it as a string; you have to do the same with prepared statements, and use PDO::PARAM_STR, like you did in the portion of code you proposed.

And for the “timestamp”, if by “timestamp” you mean:

• The MySQL timestamp data-type: it’s the same, you’ll pass it as a string
• The PHP Unix timestamp, which is an integer: you’ll pass it an int.

Method 2

Simply creating the date using php date function should fix this issue for you.

$handle->execute(array(":date"=>date("Y-m-d H:i:s", strtotime($date)), PDO::PARAM_STR));

Edit: Please note though, that strtotime
(http://php.net/manual/en/function.strtotime.php) can’t handle every
kind of date formats.

Method 3

Nope. Treat date as a string.

Method 4

You have to treat the date as string, but you can create a function to check if is a valid date before pass it as a param.
Like this:

function checkValidDate($date, $format = "dd-mm-yyyy"){
            if($format === "dd-mm-yyyy"){
            $day = (int) substr($date,0,2);
            $month = (int) substr($date, 3,2);
            $year = (int) substr($date, 6,4);

        }else if($format === "yyyy-mm-dd"){
            $day = (int) substr($date,8,2);
            $month = (int) substr($date, 5,2);
            $year = (int) substr($date, 0,4);
        }

        return checkdate($month, $day, $year);
}

Method 5

A complete section to properly store a DateTime value (as a string) in a RDBMS:

/** @const string app_date_format expected date format in the PHP domain (Swiss) */
define( 'app_date_format', 'd.m.Y' ); 

/** @var PDOConnection $db */
$db = new PDO( $dsn, $db_user, $db_pass, $db_options );

/** @var DateTime $date */
$date = DateTime::createFromFormat( app_date_format, '30.11.2020' );

$stmt = $db-> prepare(
    "UPDATE `test`
    SET `test_date` = STR_TO_DATE(:date, '%Y-%m-%d %H:%i:%s' )
    WHERE `test`.`test_id` = :id"
);

$id = 1;
$stmt->bindValue( ':id', $id );
$stmt->bindValue( ':date', $date-> format( 'Y-m-d H:i:s'));
$stmt->execute() or die( $stmt-> errorInfo()[2] );

Tested with PHP 7.4.25; MariaDB 10.6.4

Method 6

This worked for me.

//MS SQL
$sql = "UPDATE my_table SET current_date = GETDATE() WHERE id = 43";
$statement = $pdo->prepare ($sql);
//$statement->bindParam (":date", strtotime (date ("Y-m-d H:i:s")), PDO::PARAM_STR);
$statement->execute ();

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0