STR_TO_DATE does not exist

Question

Hi,

if is use this command in mysq:

SELECT node.nid AS nid, users.uid AS users_uid, node.created AS node_created
FROM dr_wiwe_node node
LEFT JOIN dr_wiwe_content_type_classified node_data_field_classified_valid_till ON node.vid = node_data_field_classified_valid_till.vid
INNER JOIN dr_wiwe_users users ON node.uid = users.uid
WHERE (
(
node.type
IN (
'classified'
)
)
AND (
node.status & lt ; & gt;

0
)
) AND (
DATE_FORMAT(STR_TO_DATE(
node_data_field_classified_valid_till.field_classified_valid_till_value, '%Y-%m-%dT%T'
), '%Y-%m-%d\T%H:%i:%s' ) & gt ; = '2010-08-15T20:55:48'
) ORDER BY node_created DESC LIMIT 0 , 60 

I am getting an error that #1305 - FUNCTION DB704128.STR_TO_DATE does not exist .

But mysql version is 5.1 anything. Where is the error?

Answer 1

I would suggest making your field_classified_valid_till_value field a datetime field. It makes sence to me to store fields containing times as such.

This way you won't need str_to_date and I guess it will make your query a little faster.

When you add data to the table you can then process it with PHP's strtotime function. http://de.php.net/manual/en/function.strtotime.php

Assuming, that PHP is tha language you use to insert new Data to the database.