Mapping of datetime format specifiers between MySQL and Oracle
|
MySQL
|
Oracle (independently from register)
|
Description
|
%a
|
DY
|
Abbreviated weekday name (Sun..Sat)
|
%b
|
MON
|
Abbreviated month name (Jan..Dec)
|
%D
|
-
|
Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.)
|
%d
%e
|
DD
|
Day of the month, numeric ((00..31) and (0..31))
|
%j
|
DDD
|
Day of year (001..366)
|
%m
%c
|
MM
|
Month, numeric ((00..12) and (0..12))
|
%M
|
MONTH
|
Month name (January..December)
|
%f
|
-
|
Microseconds (000000..999999)
|
%i
|
MI
|
Minutes, numeric (00..59)
|
%h
%I
%l
|
HH
HH12
|
Hour ((01..12) and (1..12))
|
%H
%k
|
HH24
|
Hour ((00..23) and (0..23))
|
%p
|
AM
PM
|
AM or PM
|
%r
|
-
|
Time, 12-hour (hh:mm:ss followed by AM or PM)
|
%S
%s
|
SS
|
Seconds ((00..59) and (0..59))
|
%T
|
-
|
Time, 24-hour (hh:mm:ss)
|
%u
|
WW
IW
|
Week (00..53), where Monday is the first day of week
|
%U
|
-
|
Week (00..53), where Sunday is the first day of week
|
%V
|
-
|
Week (01..53), where Sunday is the first day of week, used with %X
|
%v
|
WW
IW
|
Week (01..53), where Monday is the first day of week, used with %x
|
%W
|
DAY
|
Weekday name (Sunday..Saturday)
|
%w
|
-
|
Day of the week (0=Sunday .. 6=Saturday)
|
%X
|
-
|
Year for the week, where Sunday is the first day of the week, numeric 4
digits; used with %V
|
%x
|
-
|
Year for the week, where Monday is the first day of the week, numeric 4
digits; used with %v
|
%Y
|
YYYY
SYYYY
IYYY
|
Year, numeric, 4 digits
|
%y
|
YY
IYY
|
Year, numeric, 2 digits
|
-
|
J
|
Julian day; the number of days since January 1, 4712 BC.
|
-
|
Q
|
Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
|
-
|
RR
|
Given a year with 2 digits:
· If the year is <50 and the last 2 digits of the current year are
>=50, then the first 2 digits of the returned year are 1 greater than
the first 2 digits of the current year.
· If the year is >=50 and the last 2 digits of the current year are
<50, then the first 2 digits of the returned year are 1 less than the
first 2 digits of the current year.
|
-
|
RRRR
|
Round year. Accepts either 4-digit or 2-digit input. If 2-digit,
provides the same return as RR. If you don't want this functionality,
then simply enter the 4-digit year.
|
-
|
W
|
Week of month (1-5) where week 1 starts on the first day of the month
and ends on the seventh.
|
-
|
SSSSS
|
Seconds past midnight (0 - 86399).
|
-
|
X
|
Local radix character.
|
-
|
Y,YYY
|
Year with comma in the position.
|
-
|
YEAR
SYEAR
|
Year, spelled out; "S" prefixes BC dates with "-".
|
-
|
YYY
|
3 digits of year.
|
-
|
Y
|
1 digit of year.
|
-
|
IY
|
2 digits of ISO year.
|
-
|
I
|
1 digit of ISO year.
|
-
|
AD
A.D.
|
AD indicator with or without periods.
|
-
|
BC
B.C.
|
BC indicator with or without periods.
|
-
|
CC
SCC
|
One greater than the first two digits of a four-digit year; "S" prefixes
BC dates with "-".
For example, '20' from '1900'.
|
-
|
D
|
Day of week (1 - 7).
|
-
|
A.M.
P.M.
|
Meridian indicator with periods.
|
-
|
TZH
|
Time zone hour.
|
-
|
TZM
|
Time zone minute.
|
-
|
TZR
|
Time zone region information.
|
-
|
RM
|
|