CODEWITHSUNDEEP
phpmysqldatetimecount
Django Anonymous
Django Anonymous

Reputation: 3025

Getting date out of datetime value stored in mysql using php

I have added a field in my MySQL Database, which is a DateTime field.

Now when value get stored, it is like this:- 2012-03-31 12:13:42

I have read in one of the forum that, if i need to fetch the date only out of the datetime i should use this query:- SELECT CAST(datetime_fieldAS DATE) AS dateonly FROM etc...

Now I actually want to count the number of fields for that date, where all the dates should be equal to today's date, So i did this:-

$todayDATE = date("Y-m-d");
$TCRTcount = "SELECT COUNT(*) FROM customers WHERE CAST(`customer_regdate` 
AS DATE) AS dateonly = '$todayDATE'";
$TCRTcount_QE = mysql_query($TCRTcount, $dacreint) or die(mysql_error());
$TCRTcount_QF = mysql_fetch_array($TCRTcount_QE);
$TotalCustomersAddedToday = $TCRTcount_QF[0];

I gives me the following error:-

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 'AS dateonly = '2012-03-31'' at line 1

Upvotes: 0

Views: 1875

Answers (3)

krky
krky

Reputation: 11

In addition, you can use CURDATE() MySQL function in your queries in stead of date("Y-m-d")

Upvotes: 1

liquorvicar
liquorvicar

Reputation: 6106

I don't believe you need to cast it and if you are always after today's date (i.e. the current date) you don't need to pass the date in from PHP, you could just do:

SELECT COUNT(*) FROM customers WHERE DATE(`customer_regdate`) = CURDATE()

However there are reasons why you would want to pass in the date from PHP (testing for one) so you might want to stick with

SELECT COUNT(*) FROM customers WHERE DATE(`customer_regdate`) = '$todayDATE'

Upvotes: 1

Shakti Singh
Shakti Singh

Reputation: 86406

Remove the AS dateonly, you can not use alias in WHERE clause

SELECT COUNT(*) FROM customers WHERE CAST(`customer_regdate` 
AS DATE) = '$todayDATE';

alternatively you can also get the date from DATETIME column using DATE().

 SELECT COUNT(*) FROM customers WHERE DATE(`customer_regdate`) = '$todayDATE';

Upvotes: 3

Related Questions