Conversion of Oracle TO_CHAR(datetime) with format string to

%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,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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

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