Oracle vs. mysql syntax vs. function

description of MySQL data_add function and date format function

• Date_add (date, INTERVAL expr type) date_sub (date, INTERVAL expr type )

These functions perform date operations. Date is a DATETIME or date value used to specify the start time.  expr is an expression that specifies the time interval value to be added or subtracted from the start date. Expr is a string, and for a negative time interval, it can begin with a '-'. type is a keyword that indicates how the expression is interpreted.

Keyword Interva and type classifiers are case insensitive.

The following table shows the relationship between the type and the expr parameter:

type value	the expected Expr format
Microsecond	microseconds
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
Day	Days
WEEK	WEEKS
MONTH	MONTHS
QUARTER	Quarters
Year	Years
Second_microsecond	' SECONDS. Microseconds '
Minute_microsecond	' MINUTES. Microseconds '
Minute_second	' Minutes:seconds '
Hour_microsecond	' HOURS. Microseconds '
Hour_second	' HOURS:MINUTES:SECONDS '
Hour_minute	' Hours:minutes '
Day_microsecond	' Days. Microseconds '
Day_second	' Days HOURS:MINUTES:SECONDS '
Day_minute	' Days Hours:minutes '
Day_hour	' Days HOURS '
Year_month	' Years-months '

MySQL allows punctuation separators in any expr format. The suggested delimiter is shown in the table. If the date parameter is a date value, and your calculation includes only the year, month, and day portions (that is, there is no time part), the result is a date value. Otherwise, the result will be a datetime value.

If the expression at the other end is a date or datetime value, the interval expr type is allowed only at both ends of the + operator. For the-operator, the INTERVAL expr type is only allowed on its right side, because extracting a date or datetime value from a time interval is meaningless. (see the example below).

mysql> SELECT ' 1997-12-31 23:59:59 ' + INTERVAL 1 SECOND;

' 1998-01-01 00:00:00 '

mysql> SELECT INTERVAL 1 day + ' 1997-12-31 ';

' 1998-01-01 '

mysql> SELECT ' 1998-01-01 '-INTERVAL 1 SECOND;

' 1997-12-31 23:59:59 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

INTERVAL 1 SECOND);

' 1998-01-01 00:00:00 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

- INTERVAL 1 day);

' 1998-01-01 23:59:59 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

- INTERVAL ' 1:1 ' minute_second);

' 1998-01-01 00:01:00 '

Mysql> SELECT date_sub (' 1998-01-01 00:00:00 ',

INTERVAL ' 1 1:1:1 ' Day_second);

' 1997-12-30 22:58:59 '

Mysql> SELECT date_add (' 1998-01-01 00:00:00 ',

-INTERVAL '-1 ' day_hour);

' 1997-12-30 14:00:00 '

Mysql> SELECT date_sub (' 1998-01-02 ', INTERVAL);

' 1997-12-02 '

Mysql> SELECT date_add (' 1992-12-31 23:59:59.000002 ',

INTERVAL ' 1.999999 ' Second_microsecond);

' 1993-01-01 00:00:01.000001 '

If you specify a time interval value that is too short (excluding all the time intervals that the type keyword expects), MySQL assumes that you have omitted the leftmost part of the time interval value. For example, if you specify a type of Day_second, the value of expr should be expected to have days, hours, minutes, and seconds. If you specify a value like ' 1:10 ', MySQL assumes that the day and hour parts do not exist, then this value represents minutes and seconds. In other words, ' 1:10 ' day_second is interpreted as equivalent to ' 1:10 ' Minute_second. This is equivalent to MySQL interpreting the time value as an explanation of how long it takes to spend rather than the day.

If you add or subtract some content that contains time parts to a date value, the result is automatically converted to a DateTime value:

Mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 day);

' 1999-01-02 '

Mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 HOUR);

' 1999-01-01 01:00:00 '

If you use a date with a badly formatted error, the result is NULL. If you add month, year_month, or year, and the date of the result date is greater than the day of the added month, the date is automatically adjusted to the maximum date that the month was added:

Mysql> SELECT date_add (' 1998-01-30 ', INTERVAL 1 MONTH);

' 1998-02-28 '

• Date_format (date,FORMAT)

Arranges the format of the date value according to the format string.

The following specifiers are available in the format string:

Specifiers	Description
%a	Abbreviated name of the weekday (Sun.. Sat)
%b	Abbreviated name of the month (Jan: DEC)
%c	month, digital form (0..12)
%d	Date of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d	Date of the month, digital form (00..31)
%e	Date of the month, digital form (0..31)
%f	microseconds (000000..999999)
%H	Hours (00..23)
%h	Hours (01..12)
%I	Hours (01..12)
%i	Minutes, digital form (00..59)
%j	Number of days in the year (001..366)
%k	Hours (0..23)
%l	Hours (1..12)
%M	Month name (January): December)
%m	month, digital form (00..12)
%p	Morning (AM) or PM (PM)
%r	Time, 12 hours (hours hh: minutes mm: seconds SS after plus am or PM)
%s	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24 hours (hours hh: minutes mm: Seconds 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), of which Sunday is the first day of the week; Simultaneous use with%x
%v	Week (01..53), of which Monday is the first day of the week; Simultaneous use with%x
%W	Weekday Name (Sunday: Saturday
%w	Daily of the Week (0= Sunday). 6= Saturday)
%x	The year of the week, of which Sunday is the first day of the week, the number form, 4 digits; and%v use simultaneously
%x	The year of the week, of which Monday is the first day of the week, the number form, 4 digits; and%v use simultaneously
%Y	Year, number form, 4 digits
%y	Year, digital form (2 digits)
%%	'% ' literal characters

All other characters are copied into the result without explanation.

Note that the '% ' character is required before the format specifier.

The range of month and date specifiers starts from zero, because MySQL allows the storage of incomplete dates such as ' 2004-00-00 '.

SELECT date_format (' 1997-10-04 22:23:00 ', '%W%M%Y ');

' Saturday October 1997 '

SELECT date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');

' 22:23:00 '

SELECT date_format (' 1997-10-04-22:23:00 ',

'%d%y%a%d%m%b%j ');

4th Sat Oct 277 '

SELECT date_format (' 1997-10-04-22:23:00 ',

'%H%k%I%r%T%s%w ');

-10:23:00 PM 22:23:00 00 6 '

SELECT date_format (' 1999-01-01 ', '%x%V ');

' 1998 52 '

a detailed comparison of the syntax differences between MySQL and Oracle

Http://www.jb51.net/article/34414.htm

Oracle vs. mysql syntax vs. function