#Written by Jen Chen
#Copyrighted by Jen Chen
#CS 122
#Lesson 3:
#Note: Portion of this lecture is from Mysql web site.
#Date Formatting:

- MySQL Date Formatting Codes 

   %M  Month name (January..December)  
   %W  Weekday name (Sunday..Saturday)  
   %D  Day of the month with english suffix (1st, 2nd, 3rd, etc.)  
   %Y  Year, numeric, 4 digits  
   %y  Year, numeric, 2 digits  
   %a  Abbreviated weekday name (Sun..Sat)  
   %d  Day of the month, numeric (00..31)  
   %e  Day of the month, numeric (0..31)  
   %m  Month, numeric (01..12)  
   %c  Month, numeric (1..12)  
   %b  Abbreviated month name (Jan..Dec)  
   %j  Day of year (001..366)  
   %H  Hour (00..23)  
   %k  Hour (0..23)  
   %h  Hour (01..12)  
   %I  Hour (01..12)  
   %l  Hour (1..12)  
   %i  Minutes, numeric (00..59)  
   %r  Time, 12-hour (hh:mm:ss [AP]M)  
   %T  Time, 24-hour (hh:mm:ss)  
   %S  Seconds (00..59)  
   %s  Seconds (00..59)  
   %p  AM or PM  
   %w  Day of the week (0=Sunday..6=Saturday)  
   %U  Week (0..52), where Sunday is the first day of the week  
   %u  Week (0..52), where Monday is the first day of the week  
   %%  A literal `%'.  

- Example:
	select CURDATE();
   will give us:

	+------------+
	| CURDATE()  |
	+------------+
	| 2004-10-07 |
	+------------+
	1 row in set (0.00 sec)

- Example:
	select CURTIME();

   will give us:
	+-----------+
	| CURTIME() |
	+-----------+
	| 14:13:03  |
	+-----------+
	1 row in set (0.00 sec)


- Example:
	select DATE_FORMAT(CURDATE(),'%M %e, %Y - %h:%i %p');

   will give us:
	+-----------------------------------------------+
	| DATE_FORMAT(CURDATE(),'%M %e, %Y - %h:%i %p') |
	+-----------------------------------------------+
	| October 7, 2004 - 12:00 AM                    |
	+-----------------------------------------------+
	1 row in set (0.00 sec)


- Example:
	select DATE_FORMAT(CURDATE(),'%b, %yy-%m-%d');

   will give us:
	+----------------------------------------+
	| DATE_FORMAT(CURDATE(),'%b, %y-%m-%d') |
	+----------------------------------------+
	| Oct, 04-10-07                         |
	+----------------------------------------+
	1 row in set (0.00 sec)

- Example:
	 select DATE_FORMAT(CURDATE(),'%W, %y-%m-%d');

   will give us:
	+---------------------------------------+
	| DATE_FORMAT(CURDATE(),'%W, %y-%m-%d') |
	+---------------------------------------+
	| Thursday, 04-10-07                    |
	+---------------------------------------+
	1 row in set (0.00 sec)

- Example:
	select DATE_FORMAT(CURDATE(),'%y-%m-%d');

   will give us:
	+-----------------------------------+
	| DATE_FORMAT(CURDATE(),'%y-%m-%d') |
	+-----------------------------------+
	| 04-10-07                          |
	+-----------------------------------+
	1 row in set (0.01 sec)


- Example:
	select DATE_FORMAT(CURDATE(),'%y-%m-%d %h:%m:%s');

   will give us:
	+--------------------------------------------+
	| DATE_FORMAT(CURDATE(),'%y-%m-%d %h:%m:%s') |
	+--------------------------------------------+
	| 04-10-07 12:10:00                          |
	+--------------------------------------------+
	1 row in set (0.00 sec)

- Example: (assume that CURDATE() returns : 2004-10-07)
	select DATE_SUB(CURDATE(), INTERVAL 30 day);
	
   will give us:
	+--------------------------------------+
	| DATE_SUB(CURDATE(), INTERVAL 30 day) |
	+--------------------------------------+
	| 2004-09-07                           |
	+--------------------------------------+
	1 row in set (0.00 sec)


- Example:
	select CURRENT_TIMESTAMP;
	
   will give us:
	+---------------------+
	| CURRENT_TIMESTAMP   |
	+---------------------+
	| 2004-10-07 14:37:05 |
	+---------------------+
	1 row in set (0.01 sec)
	 
- Example:  (assume that CURDATE() returns : 2004-10-07)
	select ADDDATE(CURDATE(), INTERVAL 30 DAY);
	
   will give us:
	+-------------------------------------+
	| ADDDATE(CURDATE(), INTERVAL 30 DAY) |
	+-------------------------------------+
	| 2004-11-06                          |
	+-------------------------------------+
	1 row in set (0.00 sec)

- Example: the function dayofmonth() will return the day of the month.

	select dayofmonth(curdate());

   will give us:
	+-----------------------+
	| dayofmonth(curdate()) |
	+-----------------------+
	|                     7 |
	+-----------------------+
	1 row in set (0.00 sec)

- Example: The function dayofweek() will return the numerical value of the date of the week;
	starting with Sunday = 1, Monday = 2, etc...

	select dayofweek(curdate());

   will give us:
	+----------------------+
	| dayofweek(curdate()) |
	+----------------------+
	|                    5 |
	+----------------------+
	1 row in set (0.00 sec)

- Example: The function dayname() will return the name of the date. Assume that today 
	is thursday.

	select dayname(curdate());

   will give us:
	+--------------------+
	| dayname(curdate()) |
	+--------------------+
	| Thursday           |
	+--------------------+
	1 row in set (0.00 sec)


- Example: The function dayofyear(DATE) returns the day of the year for date, in the range 
	1 to 366. 

   	 select dayofyear(curdate());

   will give us:
	+----------------------+
	| dayofyear(curdate()) |
	+----------------------+
	|                  281 |
	+----------------------+

- EXample: EXTRACT(type FROM date) 

	The EXTRACT() function uses the same kinds of interval type specifiers as DATE_ADD() 
	or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

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

   will give us:
	+---------------------------------+
	| EXTRACT(YEAR FROM '1999-07-02') |
	+---------------------------------+
	|                            1999 |
	+---------------------------------+

- Example:
	SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');

   will give us:
	+------------------------------------------------+
	| EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03') |
	+------------------------------------------------+
	|                                         199907 |
	+------------------------------------------------+

- Example:
	SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');

   will give us:
	+------------------------------------------------+
	| EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03') |
	+------------------------------------------------+
	|                                          20102 |
	+------------------------------------------------+

- Example:
	select from_unixtime(unix_timestamp());

   will give us:
	+---------------------------------+
	| from_unixtime(unix_timestamp()) |
	+---------------------------------+
	| 2004-10-07 14:59:12             |
	+---------------------------------+


- Example:
	select minute(curtime());

   will give us:
	+-------------------+
	| minute(curtime()) |
	+-------------------+
	|                 3 |
	+-------------------+

- Example:
	select now();

   will give us:
	+---------------------+
	| now()               |
	+---------------------+
	| 2004-10-07 15:03:46 |
	+---------------------+


- Example:
	select month(curdate());

   will give us:
	+------------------+
	| month(curdate()) |
	+------------------+
	|               10 |
	+------------------+

- Example:
	select monthname(curdate());

   will give us:
	+----------------------+
	| monthname(curdate()) |
	+----------------------+
	| October              |
	+----------------------+

- Example:
	select quarter(curdate());

   will give us:
	+--------------------+
	| quarter(curdate()) |
	+--------------------+
	|                  4 |
	+--------------------+

- Example:
	select sec_to_time(123);
	
   will give us:
	+------------------+
	| sec_to_time(123) |
	+------------------+
	| 00:02:03         |
	+------------------+


- STR_TO_DATE(str,format) 
	This is the reverse function of the DATE_FORMAT() function. It takes a string str 
and a format string format. STR_TO_DATE() returns a DATETIME value if the format string 
contains both date and time parts, or a DATE or TIME value if the string contains only 
date or time parts. The date, time, or datetime values contained in str should be given 
in the format indicated by format. For the specifiers that can be used in format, see the 
table in the DATE_FORMAT() function description. All other characters are just taken 
verbatim, thus not being interpreted. If str contains an illegal date, time, or datetime 
value, STR_TO_DATE() returns NULL. 

mysql> SELECT STR_TO_DATE('03.10.2003 09.20','%d.%m.%Y %H.%i');
        -> '2003-10-03 09:20:00'

mysql> SELECT STR_TO_DATE('10arp', '%carp');
        -> '0000-10-00 00:

mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00', '%Y-%m-%d %H:%i:%s');
        -> NULL