CODEWITHSUNDEEP
phpsqldatedatetimemysqli
sir psycho sexy
sir psycho sexy

Reputation: 800

Query over a DateTime field ignoring hours and minutes

I am writing a PHP site, in which I use some Mysql to access a DB.

I have a table named travel, which used to have a field named date from type Date (according to phpMyAdmin), and also I have a PHP variable $date from type string which stores a date in the format "Y-m-d"

when I needed to get all the elements from the table where the date was the same as the specified in $date, I was able to do it with a simple query like this:

"SELECT * FROM travel WHERE date=$date"

Now the problem is, I needed to change the date field data-type on the DB, from Date to DateTime, to also store hours and minutes.

On this particular query I am not interested on hours and minutes. I just need to get all the elements from the table where the date is the same as the specified in $date without considering the hours and minutes stored in the DB field.

what i am suposed to change to achieve this?

For example:

$date = "2030-12-25"

and

database

    ID (int)        date (DateTime)
    1           1994-05-30 12:25:00
    2           2030-12-25 15.20:00
    3           2030-12-25 10:30:00

I need to get elements with ID 2 and 3.

PS: I know there are similar questions to this, but none of them were useful to me for different reasons

Edit:

The mistake that was preventing me to make this query work properly was also present on the old implementation without the time part.

I was omitting single quotes on the $date variable, and it caused the DBMS to not identify the date as a string.

old query:

"SELECT * FROM travel WHERE date=$date"

"SELECT * FROM travel WHERE date='$date'" fixed adding single quotes on $date

Also as KIKO Software answered the new one should include quotes too:

"SELECT * FROM travel WHERE DATE(date) = '$date';"

Upvotes: 0

Views: 769

Answers (3)

Vinod Nardiya
Vinod Nardiya

Reputation: 11

You need to format the datetime field in database by using Date function.

SELECT * FROM travel WHERE DATE(date) = '2018-07-15'

Upvotes: 0

Chagai Wild
Chagai Wild

Reputation: 2163

you can use preg_match to get a substring of the date.

for example: preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$date)

Upvotes: 0

KIKO Software
KIKO Software

Reputation: 16741

Simply change:

"SELECT * FROM travel WHERE date=$date"

to:

"SELECT * FROM travel WHERE DATE(date) = '$date';"

The function DATE() gets the date part from the column date.

Upvotes: 1

Related Questions