[Resolved] select date(DateTimeField) problem?

[leatherback]

Hi,

Felling stupid here..

I am trying to get the uniq dates out of a database. But had some trouble, and simplified the query, untill I got to the most basic:

"select date(DateTimeColumName) from Table", which gives me an error (Running in phpadmin):

Error

SQL-query : 

SELECT date( ost )
FROM ivontest
LIMIT 0, 30

MySQL said:

You have an error in your SQL syntax. 
Check the manual that corresponds to your MySQL server version 
for the right syntax to use near '(ost) 
FROM ivontest LIMIT 0, 30' at line 1

Am I missing something here?

[Frederick]

The best method instead of using the date feature of mysql, insert your own date using time() and mktime() functions into a CHAR or INT field type.

<?php
$month = $_POST["month"];
$day = $_POST["day"];
$year = $_POST["year"];

$postdate = mktime(0,0,0,$month,$day,$year);
$sql = mysql_query("INSERT INTO tablename (postdate) VALUES ('$postdate')") or die (mysql_error());
?>

Grab results as follows

<?php
// example checks for postdates older then 15 days - time represents NOW
$querydate = time()-(1296000);
$sql = mysql_query("SELECT * FROM tablename WHERE postdate > '$querydate' ") or die (mysql_error());
?>

[leatherback]

Hi Frederick,

Thank you for your reply,

But I am trying to find out why select date() from does not work. Not for other ways to store my data.

Any other ideas anyone?

J.

[xblue]

leatherback,

the only other ideas I have

a) an issue with the mysql version ("DATE() is available as of MySQL 4.1.1.")

b) something wrong with the column name (but unlikely, I think the error message should look slightly different then)

[typo edited]

[Roger_Ramjet]

Definitely a version problem. Have to use date_format instead if it is pre 4.1.1

[leatherback]

Hey XBlue, Roger,

Thanks so much. Indeed.. I have MySQL 4.0.2 runnin here, since that is what my provider uses. Hm.. I'll have to rethink this then.

Cheers!

J.