Playing with date

Since I easily forget about MySQL date function. I wrote this to myself.

I have a field name `created` with DATETIME format like 2010-05-24 11:24:05

Using the MySQL function
SELECT NOW() give you the latest time like 2010-05-24 11:24:05

SELECT CURDATE() give you the current date like 2010-05-24

So I available command is. Lets say the current time is 2010-05-24 11:24:05
SELECT YEAR( NOW()); will return 2010
SELECT MONTH( NOW()); will return 05
SELECT DAY( NOW()); will return 24
SELECT TIME( NOW()); will return 11:24:05
SELECT HOUR( NOW()); will return 11
SELECT MINUTE( NOW()); will return 24
SELECT SECOND( NOW()); will return 05

*Note
SELECT YEAR('2010-05-24 11:24:05'); will also return 2010


To select a record in the same month
SELECT * FROM table WHERE YEAR(`created`) = YEAR(CURDATE()) AND MONTH(`created`) = MONTH(CURDATE())

Another format is like this
SELECT name, created FROM customer
WHERE created BETWEEN '2010-01-04 00:00:00' AND '2010-01-06 00:00:00';

*Take note the field registerdate format is DATETIME()
*Take note that the date range in the between must be from low to high

These is a function to take the 'current amount from stock forecast'. Let say with these situation
15 March 2011, Gold price will be 2.0
20 March 2011, Gold price will be 2.1
30 March 2011, Gold price will be 2.2

Today is 25 March 2011. So the SQL will return "Gold price will be 2.1"
So these is the SQL

"SELECT `goldprice` FROM `vtiger_goldforecast` WHERE `datestart` <=CURDATE() ORDER BY `vtiger_goldforecast`.`datestart` DESC LIMIT 1";

Update 1.2 (25 Nov 2011) - Select a record within a date
Case: The table has start date and end date. You want to retrieve a record where today is still withing the start and end date

SELECT * FROM special eventWHERE NOW( ) BETWEEN startdate AND enddate


Thats for today, version 1.1
Going to update later