Table of ContentsPreviousNext

Ispirer
Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.
You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.
Check out the relevant toolkit documentation.
Ispirer Oracle to MySQL Migration overview.


Ispirer SQLWays Database Migration Software

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
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
 

TABLE 57. Example of Conversion
Oracle
MySQL
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; 
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; 


Table of ContentsPreviousNext
Copyright 1999-2023 Ispirer Systems.
Ispirer and SQLWays are registered trademarks. All other product names may be trademarks of the respective companies.
All rights reserved.