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_formatasbegin-- GET ACTUAL TIME AND DATEselect to_char(sysdate,'DD-MON-YYYY:HH24:MI') from dual;end; | create procedure sp_to_char_date_format()begin-- GET ACTUAL TIME AND DATEselect DATE_FORMAT(CURRENT_TIMESTAMP, '%e-%M-%Y:%H:%i') from dual;end; |