CODEWITHSUNDEEP
mysqlsql
ahmet
ahmet

Reputation: 5005

Query to select the amount of rows in the database for today's date?

How can I select the amount of rows from a database if the type is a varchar?

SELECT * FROM `orders` WHERE DATE(`order_date`) = DATE(NOW())

would not work as it is not stored in datetime is there any other way?

2011-10-30 19:14:32

Is what is stored

Upvotes: 0

Views: 74

Answers (4)

Mithun Sasidharan
Mithun Sasidharan

Reputation: 20940

Use datediff function in sql....

Something like

SELECT * FROM table WHERE DATEDIFF(day, StartDate, GETDATE()) <= 0

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Best would be to change the order_date to date type and use this:

SELECT * 
FROM orders 
WHERE order_date >= CURDATE()
  AND order_date < CURDATE() + INTERVAL 1 DAY

If you keep it as VARCHAR, you can use this variation: 

SELECT * 
FROM orders 
WHERE order_date >= CAST( CURDATE() AS CHAR)
  AND order_date < CAST( (CURDATE() + INTERVAL 1 DAY) AS CHAR)

Both of these, may use an index of order_date, if there is one.

Upvotes: 1

Ankur
Ankur

Reputation: 12774

You can use

SELECT * FROM `orders` WHERE DATE(`order_date`) = CURDATE()

Upvotes: 2

stivlo
stivlo

Reputation: 85496

Since order_date is a VARCHAR, try with:

WHERE LEFT(order_date, 10) = CURDATE()

Upvotes: 1

Related Questions