Converting Dates with PHP for Datetime in SQL

Converting dates with PHP for DATETIME in SQL

Your date time format is wrong: dd/mm/yyyy hh:mm:ss. Probably you mean d/m/Y H:i:s

If you have 5.3+ version there is safe way to convert the date time into another format. Here's an example:

$timestamp = '31/05/2001 12:22:56';
$timestamp = DateTime::createFromFormat('d/m/Y H:i:s', $timestamp);
echo $timestamp->format('Y-m-d H:i:s');

or if you like more procedural way:

$timestamp = '31/05/2001 12:22:56';
$timestamp = date_create_from_format('d/m/Y H:i:s', $timestamp);
echo date_format($timestamp, 'Y-m-d H:i:s');

Be careful with previous suggestions. Some are completely wrong and others could lead to errors.

Convert from MySQL datetime to another format with PHP

If you're looking for a way to normalize a date into MySQL format, use the following

$phpdate = strtotime( $mysqldate );
$mysqldate = date( 'Y-m-d H:i:s', $phpdate );

The line $phpdate = strtotime( $mysqldate ) accepts a string and performs a series of heuristics to turn that string into a unix timestamp.

The line $mysqldate = date( 'Y-m-d H:i:s', $phpdate ) uses that timestamp and PHP's date function to turn that timestamp back into MySQL's standard date format.

(Editor Note: This answer is here because of an original question with confusing wording, and the general Google usefulness this answer provided even if it didnt' directly answer the question that now exists)

Converting from SQL datetime to PHP DateTime, using

1) If you just want to display date in correct format, then format it in sql by using DATE_FORMAT:

$describeQuery = 'SELECT username, firstname, surname, location, DATE_FORMAT(time_of_last_update, '%Y-%m-%d %H:%i:%s')      FROM location';

2) If you want it as date object in php you need to create it first, before calling method format:

// creating new date object
$date_time = new DateTime($row['time_of_last_update']);

//now you can use format on that object:
$formated_date = $date_time->format('d/m/y H:i');

Convert mssql datetime object to PHP string

This has caught me out a couple of times too.

Hopefully this will help you as it has myself :)

http://af-design.com/blog/2010/03/13/microsoft-sql-server-driver-for-php-returns-datetime-object/

Here's the gist of what that page is saying, in case the link goes down.

When querying against a column defined as a datetime, the native PHP SQL Server extension returns a string where as the Microsoft extension returns a DateTime object. So if you are expecting a string, you’ll need to adjust your code accordingly.

It goes on to explain that you can simply add an additional parameter to your requests to the database, specifying that you want your dates to be returned as strings. Simply add the ReturnDatesAsStrings parameter, specifying true.

Example:

$connectionParams = array(
    'USR'=>'user',
    'PASS'=>'pass',
    'Database'='myDatabase',
    'ReturnDatesAsStrings'=>true  //<-- This is the important line
);
$conn = sqlsrv_connect('127.0.0.1',$connectionParams);

Then you can use $conn as you would regularly for your sqlsrv database connection, only dates will return as strings, instead of DateTime objects.

Alternately, if all you wanted was a timestamp out of the datetime you could call:

$myDateTimeObject->getTimestamp();

SQL date as INTEGER to PHP datetime format

This looks like it's a variant of an MS Excel serialized timestamp, which is a count of the days since 31st December 1899.... if that is the case, then you can convert it to a unix timestamp using a function like:

function ExcelToUnixTimestamp($dateValue = 0)
{
    $excelBaseDate = 25567;

    // Perform conversion
    if ($dateValue >= 1) {
        $utcDays = $dateValue - $excelBaseDate;
        $returnValue = round($utcDays * 86400);
        if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
            $returnValue = (integer) $returnValue;
        }
    } else {
        $hours = round($dateValue * 24);
        $mins = round($dateValue * 1440) - round($hours * 60);
        $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
        $returnValue = (integer) gmmktime($hours, $mins, $secs);
    }

    return $returnValue;
}

$weirdTimestamp = 75668;
$unixTimestamp = ExcelToUnixTimestamp($weirdTimestamp - 36163);
echo date('Y-m-d H:i:s', $unixTimestamp);

PHP date() format when inserting into datetime in MySQL

The problem is that you're using 'M' and 'D', which are a textual representations, MySQL is expecting a numeric representation of the format 2010-02-06 19:30:13

Try: date('Y-m-d H:i:s') which uses the numeric equivalents.

edit: switched G to H, though it may not have impact, you probably want to use 24-hour format with leading 0s.

MSSQL datetime datetime in php

I solved the problem with a work arround, as i didnt need to see the days where there are 0 tickets, i didnt have to build an temporary table and do a left join.

The solution i came with is to use the datetime fields (bb_casestartingtime) and use those dates. Now the chart axis title matches the chart bar.

solution

Convert the $variable where the datetime field is put to, to a datetime field variable.

 $rows[] = "['".$row[1]->format('D j-m')."',".$row[0]."],";
                  ^               ^                ^
            The datetime row | The format | The amount of tickets

converting datetime field to date in sql query in laravel contorller

I think logs.created must be timestamp and laravel has several ways to select and filter by given date.

1. $query->whereDate('logs.created_at', '2020-01-25')
2. $query->whereRaw('DATE(logs.created_at) = ?', ['2020-01-25'])
3. $query->where(DB::raw('DATE(logs.created_at)'), '2020-01-25')

mysql DATE function converts date from given timestamp (or string) with default format Y-m-d or yyyy-mm-dd

Hope this helps you

Convert a date format in PHP

Use strtotime() and date():

$originalDate = "2010-03-21";
$newDate = date("d-m-Y", strtotime($originalDate));

(See the strtotime and date documentation on the PHP site.)

Note that this was a quick solution to the original question. For more extensive conversions, you should really be using the DateTime class to parse and format :-)

Convert Date To sql formate in php and store it to database

use MySQL Str_To_Date :-

SELECT str_to_date('13 November 2015','%d %M %Y')

or try :-

$date=$_POST['date'];

insert into table (col) values (str_to_date($date,'%d %M %Y'))

or use PHP strtotime

$time = strtotime('13 November 2015');
$newformat = date('Y-m-d',$time);

---

Related Topics