Oracle 5.0, Справочное руководство

Страница: 941 / 3232

Date and Time Functions
921
To ensure interpretation of the interval value as you expect, a
CAST()
[948] operation may be
used. To treat
6/4
as 1 hour, 5 minutes, cast it to a
DECIMAL
value with a single fractional digit:
mysql> SELECT CAST(6/4 AS DECIMAL(3,1));
-> 1.5
mysql> SELECT DATE_ADD('1970-01-01 12:00:00',
-> INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
-> '1970-01-01 13:05:00'
If you add to or subtract from a date value something that contains a time part, the result is
automatically converted to a datetime value:
mysql> SELECT DATE_ADD('2013-01-01', INTERVAL 1 DAY);
-> '2013-01-02'
mysql> SELECT DATE_ADD('2013-01-01', INTERVAL 1 HOUR);
-> '2013-01-01 01:00:00'
If you add
MONTH
,
YEAR_MONTH
, or
YEAR
and the resulting date has a day that is larger than the
maximum day for the new month, the day is adjusted to the maximum days in the new month:
mysql> SELECT DATE_ADD('2009-01-30', INTERVAL 1 MONTH);
-> '2009-02-28'
Date arithmetic operations require complete dates and do not work with incomplete dates such as
'2006-07-00'
or badly malformed dates:
mysql> SELECT DATE_ADD('2006-07-00', INTERVAL 1 DAY);
-> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
-> NULL
•
DATE_FORMAT(date,format)
[921]
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
)