Getting date out of datetime value stored in mysql using php

Question

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

Shakti Singh · Accepted Answer · 2012-03-31 08:17:34Z

3

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';

answered Mar 31, 2012 at 8:17

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

liquorvicar · Accepted Answer · 2012-03-31 08:21:37Z

1

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'

answered Mar 31, 2012 at 8:21

liquorvicar

6,1462 gold badges19 silver badges21 bronze badges

Comments

krky · Accepted Answer · 2012-03-31 08:27:55Z

1

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

answered Mar 31, 2012 at 8:27

krky

111 bronze badge

Collectives™ on Stack Overflow

Getting date out of datetime value stored in mysql using php

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related