Using STR_to_DATE in PHP SQL Query

Question

I need a way of changing the format the date format from datetime to just the date in an SQL query, I have tried:

$sql="SELECT created_by FROM meetings WHERE STR_TO_DATE('date_start', 'Y-m-d')='$CorrectDate'";

but to no avail.

date_start is the field in the SQL table.

Any help welcome!!!

You don't say what your DBMS is: MySQL? Oracle? SQL Server? – Álvaro González Jan 31 '11 at 17:48 — Álvaro González, Jan 31 '11 at 17:48
@Alvaro: `STR_TO_DATE` is only available in `MySQL`. – Quassnoi Jan 31 '11 at 17:49 — Quassnoi, Jan 31 '11 at 17:49

Quassnoi · Answer 1 · 2011-01-31T17:47:05.690

2

SELECT  created_by
FROM    meetings
WHERE   date_start >= $correctDate
        AND date_start < $correctDate + INTERVAL 1 DAY

Unlike any solution which involves a function or an expression over date_start, this one is sargable, i. e. able to use an index on date_start efficiently.

edited Jan 31 '11 at 17:47

answered Jan 31 '11 at 17:42

Quassnoi

413,100
91
616
614

if the format in SQL is Datetime, would this not still return the time also – Bift Jan 31 '11 at 17:45
@Bift: return where? Is `created_by` a date column also? – Quassnoi Jan 31 '11 at 17:47

Using STR_to_DATE in PHP SQL Query

1 Answers1