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, 12hour (hh:mm:ss followed by AM or PM)

%S
%s

SS

Seconds ((00..59) and (0..59))

%T



Time, 24hour (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; JANMAR = 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 4digit or 2digit input. If 2digit, provides the same return as RR. If you don't want this functionality, then simply enter the 4digit year.



W

Week of month (15) 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 fourdigit 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

