SQL: Today's Date

Kristopher

What's wrong with this SQL statement?:

SELECT * FROM vbsPosts WHERE createdBy='Dave' AND createdOn = CURDATE()

I want to select records that have field createdBy equal to Dave and field createdOn equal to today's date. 😕

Lars_Berg

Which DBMS? Most of them have different functions for getting current date.

Kristopher

I don't get any error, it just reports 0 when I run this code:

$upt = mysql_num_rows(mysql_query("SELECT * FROM vbsPosts WHERE createdBy='Dave' AND createdOn = CURDATE()"));

It should return 3 records, but it's reporting 0. So I'm thinking I'm using CURDATE() wrong?

BTW, createdOn is a datetime field.

ultraslacker

Nothing if createdOn is a date.

If it is a datetime value, you need to compare JUST the date. Use substring or date_format.

ultraslacker

Okay - SUBSTRING(createdOn, 1,10)

Kristopher

Originally posted by ultraslacker
Nothing if createdOn is a date.

If it is a datetime value, you need to compare JUST the date. Use substring or date_format.

Good call. I'll give that a try. Thanks alot!

Kristopher

SELECT * FROM vbsPosts WHERE createdBy='Dave' AND DATE_FORMAT(createdOn, '%Y-%m-%d') = CURDATE()

Works fine when I run it in phpMyAdmin, but when I try to use it in a script with mysql_num_rows, it gives me 0?

Kristopher

Nevermind. I got it working great. Thanks for your help. 🙂