Conversion of Oracle TO_CHAR(datetime) with format string to MysqL
The Oracle TO_CHAR(datetime,fmt) function converts datetime values to a string in the format specified by the fmt option.
MysqL has the DATE_FORMAT function that allows datetime values converting to a string in the specified format.
sqlWays converts the Oracle TO_CHAR function to the MysqL DATE_FORMAT function and converts elements of format string from Oracle to corresponding specifier in MysqL as specified in the following table
TABLE 56.Conversion of Oracle TO_CHAR(datetime) with format string to MysqL
@H_502_16@
Mapping of datetime format specifiers between MysqL and Oracle
@H_502_16@
MysqL
@H_502_16@
Oracle (independently from register)
@H_502_16@
Description
@H_502_16@
@H_502_16@
|
%a
@H_502_16@ |
DY
@H_502_16@ |
Abbreviated weekday name (Sun..Sat)
@H_502_16@ |
|
%b
@H_502_16@ |
MON
@H_502_16@ |
Abbreviated month name (Jan..Dec)
@H_502_16@ |
|
%D
@H_502_16@ |
-
@H_502_16@ |
Day of the month with English suffix (0th,1st,2nd,3rd,etc.)
@H_502_16@ |
|
Day of the month,numeric ((00..31) and (0..31))
@H_502_16@ |
||
|
%j
@H_502_16@ |
DDD
@H_502_16@ |
Day of year (001..366)
@H_502_16@ |
|
MM
@H_502_16@ |
Month,numeric ((00..12) and (0..12))
@H_502_16@ |
|
|
%M
@H_502_16@ |
MONTH
@H_502_16@ |
Month name (January..December)
@H_502_16@ |
|
%f
@H_502_16@ |
-
@H_502_16@ |
Microseconds (000000..999999)
@H_502_16@ |
|
%i
@H_502_16@ |
MI
@H_502_16@ |
Minutes,numeric (00..59)
@H_502_16@ |
|
Hour ((01..12) and (1..12))
@H_502_16@ |
||
|
HH24
@H_502_16@ |
Hour ((00..23) and (0..23))
@H_502_16@ |
|
|
%p
@H_502_16@ |
AM or PM
@H_502_16@ |
|
|
%r
@H_502_16@ |
-
@H_502_16@ |
Time,12-hour (hh:mm:ss followed by AM or PM)
@H_502_16@ |
|
SS
@H_502_16@ |
Seconds ((00..59) and (0..59))
@H_502_16@ |
|
|
%T
@H_502_16@ |
-
@H_502_16@ |
Time,24-hour (hh:mm:ss)
@H_502_16@ |
|
%u
@H_502_16@ |
Week (00..53),where Monday is the first day of week
@H_502_16@ |
|
|
%U
@H_502_16@ |
-
@H_502_16@ |
Week (00..53),where Sunday is the first day of week
@H_502_16@ |
|
%V
@H_502_16@ |
-
@H_502_16@ |
Week (01..53),where Sunday is the first day of week,used with %X
@H_502_16@ |
|
%v
@H_502_16@ |
Week (01..53),where Monday is the first day of week,used with %x
@H_502_16@ |
|
|
%W
@H_502_16@ |
DAY
@H_502_16@ |
Weekday name (Sunday..Saturday)
@H_502_16@ |
|
%w
@H_502_16@ |
-
@H_502_16@ |
@L_403_81@
Day of the week (0=Sunday .. 6=Saturday)
@H_502_16@ |
|
%X
@H_502_16@ |
-
@H_502_16@ |
Year for the week,where Sunday is the first day of the week,numeric 4 digits; used with %V
@H_502_16@ |
|
%x
@H_502_16@ |
-
@H_502_16@ |
Year for the week,where Monday is the first day of the week,numeric 4 digits; used with %v
@H_502_16@ |
|
%Y
@H_502_16@ |
Year,numeric,4 digits
@H_502_16@ |
|
|
%y
@H_502_16@ |
Year,2 digits
@H_502_16@ |
|
|
-
@H_502_16@ |
J
@H_502_16@ |
Julian day; the number of days since January 1,4712 BC.
@H_502_16@ |
|
-
@H_502_16@ |
Q
@H_502_16@ |
Quarter of year (1,2,3,4; JAN-MAR = 1).
@H_502_16@ |
|
-
@H_502_16@ |
RR
@H_502_16@ |
Given a year with 2 digits:
@H_502_16@
� 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.
@H_502_16@
� 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.
@H_502_16@ |
|
-
@H_502_16@ |
RRRR
@H_502_16@ |
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.
@H_502_16@ |
|
-
@H_502_16@ |
W
@H_502_16@ |
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
@H_502_16@ |
|
-
@H_502_16@ |
SSSSS
@H_502_16@ |
Seconds past midnight (0 - 86399).
@H_502_16@ |
|
-
@H_502_16@ |
X
@H_502_16@ |
Local radix character.
@H_502_16@ |
|
-
@H_502_16@ |
Y,YYY
@H_502_16@ |
Year with comma in the position.
@H_502_16@ |
|
-
@H_502_16@ |
Year,spelled out; "S" prefixes BC dates with "-".
@H_502_16@ |
|
|
-
@H_502_16@ |
YYY
@H_502_16@ |
3 digits of year.
@H_502_16@ |
|
-
@H_502_16@ |
Y
@H_502_16@ |
1 digit of year.
@H_502_16@ |
|
-
@H_502_16@ |
IY
@H_502_16@ |
2 digits of ISO year.
@H_502_16@ |
|
-
@H_502_16@ |
I
@H_502_16@ |
1 digit of ISO year.
@H_502_16@ |
|
-
@H_502_16@ |
AD indicator with or without periods.
@H_502_16@ |
|
|
-
@H_502_16@ |
BC indicator with or without periods.
@H_502_16@ |
|
|
-
@H_502_16@ |
||
|
-
@H_502_16@ |
D
@H_502_16@ |
Day of week (1 - 7).
@H_502_16@ |
|
-
@H_502_16@ |
Meridian indicator with periods.
@H_502_16@ |
|
|
-
@H_502_16@ |
TZH
@H_502_16@ |
Time zone hour.
@H_502_16@ |
|
-
@H_502_16@ |
@L_404_163@
TZM
@H_502_16@ |
Time zone minute.
@H_502_16@ |
|
-
@H_502_16@ |
TZR
@H_502_16@ |
Time zone region information.
@H_502_16@ |
|
-
@H_502_16@ |
RM
@H_502_16@ |
@H_502_16@ |
TABLE 57.Example of Conversion
@H_502_16@
Oracle
@H_502_16@
MysqL
@H_502_16@
@H_502_16@
create procedure sp_to_char_date_format as begin -- GET ACTUAL TIME AND DATE select to_char(sysdate,'DD-MON-YYYY:HH24:MI') from dual; end;@H_502_16@ |
create procedure sp_to_char_date_format() begin -- GET ACTUAL TIME AND DATE select DATE_FORMAT(CURRENT_TIMESTAMP,'%e-%M- %Y:%H:%i') from dual; end;@H_502_16@ |
