October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You want to obtain just a part of a date or a time.
Treat a temporal value as a string and use a function such as
LEFT( ) or MID( ) to extract
substrings corresponding to the desired part of the value.
Recipe 5.5 and Recipe 5.6
discuss how to extract components of temporal values using
DATE_FORMAT( ) or functions such as YEAR( ) and MONTH( ). If you pass a date or
time value to a string function, MySQL treats it as a string, which
means you can extract substrings. Thus, yet another way to extract
pieces of temporal values is to use string functions such as
LEFT( ) or MID( ).
mysql>SELECT dt,->LEFT(dt,4) AS year,->MID(dt,9,2) AS day,->RIGHT(dt,2) AS second->FROM datetime_val;+---------------------+------+------+--------+ | dt | year | day | second | +---------------------+------+------+--------+ | 1970-01-01 00:00:00 | 1970 | 01 | 00 | | 1987-03-05 12:30:15 | 1987 | 05 | 15 | | 1999-12-31 09:00:00 | 1999 | 31 | 00 | | 2000-06-04 15:45:30 | 2000 | 04 | 30 | +---------------------+------+------+--------+
You can pull out the entire date or time part from
DATETIME values using string-extraction functions
such as LEFT( ) or RIGHT( ):
mysql>SELECT dt,->LEFT(dt,10) AS date,->RIGHT(dt,8) AS time->FROM datetime_val;+---------------------+------------+----------+ | dt | date | time | +---------------------+------------+----------+ | 1970-01-01 00:00:00 | 1970-01-01 | 00:00:00 | | 1987-03-05 12:30:15 ...