Convert DATE to DATETIME in php and mySQL

Question

In my mySQL database I have a field called FlowerOpen that is in the DATE format (YYYY-MM-DD) in my php script, I want to convert it so the user sees Monday, May 28, 2012

I keep finding explanations on how to convert DATETIME to DATE but not DATE to DATETIME

I tried converting the date with the following command:

$Date = DATE_FORMAT($ResultsLists['FlowerOpen'],'%W, %M %e, %Y');

I keep getting the following error:

Warning: date_format() expects parameter 1 to be DateTime, string given in
C:\xampp\htdocs\Orchid\viewPlantInfo.php on line 383

So I tried converting the FlowerOpen to DATETIME and then format it using:

$Date = date("Y-m-d H:i:s", strtotime($ResultsLists['FlowerOpen']));
$Date = DATE_FORMAT($Date,'%W, %M %e, %Y');

but I got the same error message.

I also tried

$Date = SELECT CAST($ResultsLists['FlowerOpen'] AS DATETIME);
$Date = DATE_FORMAT($Date,'%W, %M %e, %Y');

but I got the error

Parse error: syntax error, unexpected T_STRING in 
C:\xampp\htdocs\Orchid\viewPlantInfo.php on line 382 (the CAST line)

The basic problem appears to be that you're mixing PHP and SQL without understand which is which and what engine is executing what code. — chaos
– chaos, Commented May 28, 2012 at 19:49
You can't use php functions in mysql, and you can't use mysql functions in PHP. They're two completely separate systems and languages that happen to be able to talk to each other. — Marc B
– Marc B, Commented May 28, 2012 at 19:51
Yes, I admit, I sometimes get mysql and php commands confused, all my scripts are being written in php. I am still new to both languages. — CampSoup1988
– CampSoup1988, Commented May 28, 2012 at 20:10

Sarfraz · Accepted Answer · 2012-05-28 19:57:30Z

1

You can convert it via php using strtotime like this:

echo date('l, M d, Y', strtotime($yourDate));

Working Example

echo date('l, M d, Y', strtotime('2012-05-29')); // Tuesday, May 29, 2012

edited May 28, 2012 at 19:57

answered May 28, 2012 at 19:52

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CampSoup1988 Over a year ago

ahhh, hits head I did not think to echo it. I also did not realize that the date function was able to convert it for me. This did it for me! Thank you!

CampSoup1988 Over a year ago

Also, thank you for editing my question, I cant seem to get a hang of stack overflow's syntax highlighting, and if i was posting more then a couple lines, ending up throwing the code into pastebin and linking it

Sarfraz Over a year ago

@CampSoup1988: There is guideline to the right side of the textbox on how to format it. You can press Ctrl+K for code.

CampSoup1988 Over a year ago

True, but when I am copying a good portion of my site, it seemed to break up my code unnaturally and i think removing parts of it. I dont recall off hand.

Community · Accepted Answer · 2023-11-17 19:55:19Z

0

you can store your DATETIME in mysql by converting them to unix timestamp format using mktime() function the when you retrive it back from database you can use date() function to convert it to your required format .

P.S converting datatime to unix timestamp before storing it in database can give you numerous advatages later when you want to work with dates

edited Nov 17, 2023 at 19:55

CommunityBot

11 silver badge

answered May 28, 2012 at 20:13

sohaan

2611 gold badge4 silver badges9 bronze badges

3 Comments

CampSoup1988 Over a year ago

I was using html5 input type=date to store the data into the database

sohaan Over a year ago

how does that make difference

CampSoup1988 Over a year ago

I did not convert the value of the input before storing it in the database

Collectives™ on Stack Overflow

Convert DATE to DATETIME in php and mySQL

2 Answers 2

Working Example

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related