Oracle 5.0, Reference Manual

Page: 947 / 3232

Date and Time Functions
927
-> 11
•
QUARTER(date)
[927]
Returns the quarter of the year for
date
, in the range
1
to
4
.
mysql> SELECT QUARTER('2008-04-01');
-> 2
•
SECOND(time)
[927]
Returns the second for
time
, in the range
0
to
59
.
mysql> SELECT SECOND('10:05:03');
-> 3
•
SEC_TO_TIME(seconds)
[927]
Returns the
seconds
argument, converted to hours, minutes, and seconds, as a
TIME
value. The
range of the result is constrained to that of the
TIME
data type. A warning occurs if the argument
corresponds to a value outside that range.
mysql> SELECT SEC_TO_TIME(2378);
-> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
•
STR_TO_DATE(str,format)
[927]
This is the inverse of the
DATE_FORMAT()
[921] function. It takes a string
str
and a format string
format
.
STR_TO_DATE()
[927] 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. If the date,
time, or datetime value extracted from
str
is illegal,
STR_TO_DATE()
[927] returns
NULL
and, as
of MySQL 5.0.3, produces a warning.
The server scans
str
attempting to match
format
to it. The format string can contain literal
characters and format specifiers beginning with
%
. Literal characters in
format
must match literally
in
str
. Format specifiers in
format
must match a date or time part in
str
. For the specifiers that
can be used in
format
, see the
DATE_FORMAT()
[921] function description.
mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
-> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
-> '2013-05-01'
Scanning starts at the beginning of
str
and fails if
format
is found not to match. Extra characters at
the end of
str
are ignored.
mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
-> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
-> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
-> '09:30:17'
Unspecified date or time parts have a value of 0, so incompletely specified values in
str
produce a
result with some or all parts set to 0:
mysql> SELECT STR_TO_DATE('abc','abc');
-> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
-> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
-> '00:00:09'