What is the NOW() function in a MySQL DateTime field query?

[aewhale]

I have a monitoring script which I am trying to better understand. One of the queries is built as follows:
[code=perl]
# delete any rows that expired more than a year ago
my $rows = $dbh->do("DELETE FROM table1 WHERE last_update < NOW() - INTERVAL 2 YEAR ");
[/code]
What is the NOW() function, and what Perl module does it come in? Is there more functions?

[Atli]

Hi.

NOW() is a MySQL function, not a Perl function.
It returns a DateTime value for the current date and time.

Check out Functions and Operators in the MySQL reference manual.

[aewhale]

Can you tell me why this is an incorrect statement?
[code=mysql]
SELECT count( )
FROM `table1`
WHERE origin_type = 'MANUAL'
AND DATE( 'create_time' ) = CURDATE( )
[/code]
MySQL said: Documentation
#1064 - 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 ') FROM `table1` WHERE origin_type = 'MANUAL' and DATE('create_ti me') = CURD' at line 1

create_time is a DateTime field. I want to be able to select the records which match the current date.

[Atli]

You need to specify a column for the count() function.
Like, for example:
[code=mysql]
SELECT count(*) FROM myTable;
[/code]
The wild-card char will always work, no matter how you table is structured.

Also, if the 'create_time' part of your DATE() function is a column name, you are going to want to enclose it in back-ticks(`) rather than single-quotes(').

Only strings are quoted. The back-ticks are used for column, table and database names that contain characters that would create problems for the parser, like spaces.
Like:
[code=mysql]
SELECT count(*) FROM `My Table`;
[/code]

P.S.
Please put all code examples inside [code] tags.
Thanks.

[aewhale]

You need to specify a column for the count() function.
Like, for example:
[code=mysql]
SELECT count(*) FROM myTable;
[/code]
The wild-card char will always work, no matter how you table is structured.

Ouch, that was what I missed. Thank you.

Also, if the 'create_time' part of your DATE() function is a column name, you are going to want to enclose it in back-ticks(`) rather than single-quotes(').

Only strings are quoted. The back-ticks are used for column, table and database names that contain characters that would create problems for the parser, like spaces.
Like:
[code=mysql]
SELECT count(*) FROM `My Table`;
[/code]

P.S.
Please put all code examples inside [code] tags.
Thanks.

Thank you for the explanation, I was wondering about the meaning(s) for the tick, quote, and double quotes.

[Atli]

Glad I could help :)