SQL query not reading date as string

Question

This piece of code works

$date = "12/09/2016";
$sql = "SELECT  * FROM someTable.forum_posts_queue where post_owner='admin' and post_create_time = '".$date."' order by post_id desc limit 0,1";

However, I wanted to set the date as a variable and pass it in as a string

$date = date("m/d/Y");
$sql = "SELECT  * FROM someTable.forum_posts_queue where post_owner='admin' and post_create_time = '".$date."' order by post_id desc limit 0,1";

This doesn't read the date as the same above, and doesn't understand the query.

MYSQL expects dates to be in the Logical format YYYY-MM-DD are you sure $date = "12/09/2016"; works? The format MM-DD-YYYY isn't logical anywhere, its just the way Americans say a date and the confusion is something we call have to put up with — RiggsFolly
– RiggsFolly, Commented Dec 9, 2016 at 15:39
Yes! I stored it as a string that would match the syntax of the date("12/09/2016") and run a cron job to get the queued post for the day — brosephBear
– brosephBear, Commented Dec 9, 2016 at 15:45
@brosephBear you should use MySQL's built-in date/time functions. They will serve you well. dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html — Funk Forty Niner
– Funk Forty Niner, Commented Dec 9, 2016 at 15:46
Final comment: Store dates as DATETIME or DATE or TIME or TIMESTAMP Then MYSQL does almost all the work for you. — RiggsFolly
– RiggsFolly, Commented Dec 9, 2016 at 15:46

Kristian Lilov · Accepted Answer · 2016-12-09 15:53:07Z

1

Using

$date = date("Y-m-d");

Should solve your problem (assuming that the type of "post_create_time" field is of DATE, DATETIME or TIMESTAMP).

answered Dec 9, 2016 at 15:53

Kristian Lilov

6106 silver badges12 bronze badges

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

2 Comments

CptMisery Over a year ago

This is the answer. Use this format for your dates in the database. If you don't like it, you will learn to.

brosephBear Over a year ago

Ok, that is helpful, but it was the fact that I was calling a string and checking to see if the current day was the string equivalence. Although, I solved it by standardizing it. I could have used a better answer of converting a date to string or the opposite of this: ` $schedule = mysqli_real_escape_string($link,$_POST['date']); $schedule = date('Y-m-d', strtotime($schedule));`

Collectives™ on Stack Overflow

SQL query not reading date as string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related