MySQL Date and Time Functions (Date Sample Query)

Date and Time Functions (sysdate,now,datetime column)

12.7. Date and Time Functions

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html




The possible values for the first and second arguments result in several possible format strings (for the specifiers used, see the table in the DATE_FORMAT() function description). ISO format refers to ISO 9075, not ISO 8601.
Function Call Result
GET_FORMAT(DATE,'USA') '%m.%d.%Y'
GET_FORMAT(DATE,'JIS') '%Y-%m-%d'
GET_FORMAT(DATE,'ISO') '%Y-%m-%d'
GET_FORMAT(DATE,'EUR') '%d.%m.%Y'
GET_FORMAT(DATE,'INTERNAL') '%Y%m%d'
GET_FORMAT(DATETIME,'USA') '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'JIS') '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'ISO') '%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'EUR') '%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'INTERNAL') '%Y%m%d%H%i%s'
GET_FORMAT(TIME,'USA') '%h:%i:%s %p'
GET_FORMAT(TIME,'JIS') '%H:%i:%s'
GET_FORMAT(TIME,'ISO') '%H:%i:%s'
GET_FORMAT(TIME,'EUR') '%H.%i.%s'
GET_FORMAT(TIME,'INTERNAL') '%H%i%s'
TIMESTAMP can also be used as the first argument to GET_FORMAT(), in which case the function returns the same values as for DATETIME.
mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
        -> '03.10.2003'
mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
        -> '2003-10-31'

# 또다른 방법 

DATE_FORMAT(date,format)
          Formats the date value according to          the format string.      
          The following specifiers may be used in the          format string. The          “%” character is required          before format specifier characters.
Specifier Description
%a Abbreviated weekday name                  (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th,                  1st, 2nd,                  3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by                  AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the                  first day of the week
%u Week (00..53), where Monday is the                  first day of the week
%V Week (01..53), where Sunday is the                  first day of the week; used with %X
%v Week (01..53), where Monday is the                  first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week                  (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric,                  four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric,                  four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character
%x x, for any                  “x” not listed above
          Ranges for the month and day specifiers begin with zero due to          the fact that MySQL permits the storing of incomplete dates          such as '2014-00-00'.      
          The language used for day and month names and abbreviations is          controlled by the value of the          lc_time_names system variable          (Section 10.7, “MySQL Server Locale Support”).      
DATE_FORMAT() returns a string          with a character set and collation given by          character_set_connection and          collation_connection so that          it can return month and weekday names containing non-ASCII          characters.      

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'

mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'

mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

* Useful Query ) 

1 ) Date To String 01
SELECT DATE_FORMAT(sysdate(),GET_FORMAT(DATETIME,'INTERNAL'));
--20121010153039
SELECT DATE_FORMAT(sysdate(),GET_FORMAT(TIME,'INTERNAL'));
--153039

2 ) Date To String 02

//yyyymmddhh24miss

select date_format(sysdate(),'%Y%m%d%H%i%s');

//hh24miss

select date_format(sysdate(),'%H%i%s');

3) String to Date

Function : STR_TO_DATE(str,format)

SELECT STR_TO_DATE('20080214', '%Y%m%d');

-> 2008-02-14 오전 12:00:00

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');

        -> '0000-00-00'

4) Sample  Query

date_add(curdate()+0,INTERVAL 30 DAY) // 30일 더하기

datediff(curdate()+0,"20121201") // datediff(A,B) A-B 해서 결과 돌려준다. 

* 한달전 where reg_date >= date_add(now(), interval -1 month)  
* 하루전 where reg_date >= date_add(now(), interval -1 day)  
* 한시간전 where reg_date >= date_add(now(), interval -1 hour)  * 30분 후 select date_add(sysdate(),interval 30 minute)

* Sample Query )

select no,pg_authkey,com_tel from g4_paycube_bill a
where is_rebill = 'Y' and pg_cancel = 'N'
and datediff(date_add(pg_last_rebill,interval +1 month),sysdate()) = 0
and not exists(
select 1 from g4_paycube_rebill where bill_no = a.no and pg_month = '201304' and (pg_stat = 'Y' or pg_cancel = 'Y')
)