Monday, November 14, 2011

SQL: Date



//
SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

//
SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
//
SELECT ADDDATE('1998-01-02', 31);
//
SELECT CURDATE();
        -> '1997-12-15'
//
mysql> SELECT CURDATE() + 0;
        -> 19971215

//
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
        -> 1

//
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
        -> '1998-01-01'

//
mysql> SELECT DAYOFMONTH('1998-02-03');
        -> 3

//mysql> SELECT DAYOFWEEK('1998-02-03');
        -> 3

//
mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
       -> 1999

//
mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
       -> 199907

//
mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');
       -> 20102


//mysql> SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'

//
mysql> SELECT MONTH('1998-02-03');
        -> 2

//
mysql> SELECT MONTHNAME('1998-02-05');
        -> 'February'

//
mysql> SELECT WEEK('1998-02-20');
        -> 7


//
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0

//

No comments: