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
|
|
分享到:
相关推荐
ascii char conversion
Ravazi_Principles_of_Data_Conversion_System_Design_600dpi.pdf
NFA toDFA conversion and then take string as input and check whether String is acceptable by DFA or not
The MATLAB and Praat code files for performing voice conversion. By: Matthew Hutchinson
Dalvik doens t distinguish 64-bits types internally, so this conversion of long to int makes no sense but shall not crash the VM.
Measurement Conversion 1. Length and Distance2. Area3. Volume and Capacity4. Weight and Mass5. Speed6. Temperature
A_Brief_Introduction_to_Sigma_Delta_Conversion
数制转换程序,c语言实现,计算机概论作业
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual; TO_CHAR(SY TO_CHAR((S ---------- ---------- 2004.05.09 2004.05.10 SQL> select last_day(sysdate) from dual; ...
VC various types of conversion
chp-15-150411095737-conversion-gate01_Network_analysis_ac_conver
Conversion_of_Infix_to_Postfix 由于我们的计算机系统只能理解和使用二进制语言,因此它假定仅可在两个操作数(例如(A + B)* C(D /(J + D)))中进行假想操作。 这些复杂的关节操作可以两个操作数和一个运算符...
读取一个华氏温度的输入,输出开尔文温度 我们设计程序的步骤如下 1. 提示用户键入华氏温度值 2. 读取输入值 3. 通过关系式转换为开氏温度 4. 输出结果,结束
This is model of back to back of HVDC
内容包括:解线性代数方程组,插值,数值积分,特殊函数,函数逼近,特征值问题,数据拟合,方程求根和非线性方程组求解-VB
SOCKET通过抓包发包实现图片转换
颜色空间转换C++程序,使用多种插值方法实现颜色空间转换
土壤粒径由国际制向美国制转换的三次样条插值的程序代码
Wonderware Tag Creation UtilityCreate Wonderware Tags from RSLogixNational Instruments LabVIEW application (.VI)
我们可以通过地球两点间的经纬度计算对应两点间的求面距离