Convert Date String to MySQL Datetime Field

convert date string to mysql datetime field

First, convert the string into a timestamp:

$timestamp = strtotime($string);

Then do a

date("Y-m-d H:i:s", $timestamp);

convert date from string to mysql datetime field

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.

<?php
$dateString = str_replace("/", "-", '31/05/2017');
$timestamp = strtotime($dateString);
var_dump(strtotime($dateString));
echo $date = date("Y-m-d H:i:s", $timestamp);
?>

check below links

http://php.net/manual/en/function.strtotime.php

strtotime returning false date

Convert string date to MySQL datetime

It's easy, just use date() combined with strtotime()

$date = "Tue 2 Sep 2014";
$conv = date("Y-m-d H:i:s", strtotime($date));
echo $conv;

output:

2014-09-02 00:00:00

To omit the time just remove H:i:s.

converting MySQL datetime field to string

You cant just stuff one data type into a variable of another without converting. Some of the code won't compile under Option Strict, because it tries to do just that:

Dim cdr_date As DateTime = reader.GetDateTime(0).ToString()

It sounds like you are trying to strip the time from a db var, so get it as a DateTime then if you must use it as string somewhere convert it:

Dim myDT As DateTime
rdr.Read()

' these do the same thing - get it as date:
myDT = Convert.ToDateTime(rdr("zDateTime"))
myDT = rdr.GetDateTime(0)

A DateTime will always have a Date and a Time. To ignore the Time, use something like "MM/dd/yyyy" to ignore it either when displaying it or converting to string. SubString and the magic number of 10 is not needed.

TextBox13.Text = myDT.ToString("MM/dd/yyyy")

Or to save to a string variable:

Dim notADate As String
' from a DB Reader:
notADate = rdr.GetDateTime(0).ToString("MM/dd/yyyy")
' in general:
notADate = myDT.ToString("MM/dd/yyyy")

---

However, this: unable to convert datetime to system datetime indicates something else it wrong. The DB Provider object is apparently unable to convert the db data to a NET DateTime Type. The most likely cause is bad data.

Rather than null, MySQL can be told to store 01/01/0001 00:00:00 as a 'zero date". For this, you may need to modify your connection string to append this:

"server=....;convertzerodatetime=True;allowzerodatetime=True"

These enable the storing and conversion of otherwise illegal DateTime values. AllowZeroDateTime in particular will return a zero date time rather than throw an exception. In this case (resolved in comments), since the return was a Zero Date but not what what seen in the DB, deleting it and creating a new row resolved it.

There are many, many other options for the MySQL connection:

MySQL ConnectionString options

PHP: convert date to datetime and insert it into mysql database?

Do one thing,

$mysqlDate = date('Y-m-d H:i:s', strtotime(str_replace("/","-",$_POST['php_date'])));
echo $mysqlDate;

Give it a try,

it should work.

Note:
Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed. If, however, the year is given in a two digit format and the separator is a dash (-, the date string is parsed as y-m-d.

Source link.

Your new concern answer,

$mysqlDate = date('Y-m-d H:i:s', strtotime(str_replace("/","-",$_POST['php_date']).' + '.rand(30,60*60*24*3).' seconds'));

How to convert text datatype to datetime in mysql?

If the original data is not in MySQL Datetime format (YYYY-MM-DD HH:MM:SS), you cannot just change the column datatype from Varchar/Text to Date/Datetime. Otherwise, there will be an irreparable Data loss.

This will be a multi-step process. You will first need to convert the date string to MySQL date format (YYYY-MM-DD HH:MM:SS). We can use STR_TO_DATE() function for this.

Your sample date string (18-11-15 18:21:25) is basically in %y-%m-%d %T format. Following format specifiers can be used:

• %d Day of the month as a numeric value (01 to 31)
• %m Month name as a numeric value (00 to 12)
• %y Year as a numeric, 2-digit value
• %T Time in 24 hour format (hh:mm:ss)

The query to update the date would look as follows:

UPDATE invoices  
SET created = STR_TO_DATE(created, '%y-%m-%d %T');

Now, you can use Alter Table to change the data type from Text type to Datetime.

ALTER TABLE invoices 
MODIFY COLUMN created datetime;

How to convert date in Text box to MySQL DATETIME format

If the format that your date picker is passing in is "Thursday, 7 May 2009", then the strtotime() and date() functions should work to give you a valid date to pass to MySQL:

$p = date("Y-m-d H:i:s",strtotime('Thursday, 7 May 2009'));

If your database field is a DATE, you probably only want the "Y-m-d" part. If it's a DATETIME, you'll want the "H:i:s" as well.

---

Related Topics