文档章节

Conversion of Oracle TO_CHAR(datetime) with format string to MySQL

暗之幻影
 暗之幻影
发布于 2015/09/23 16:59
字数 688
阅读 4
收藏 0

Conversion of Oracle TO_CHAR(datetime) with format string to MySQL

The Oracle TO_CHAR(datetimefmt) 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; 

本文转载自:http://depravedangel.iteye.com/blog/1455471

暗之幻影
粉丝 20
博文 377
码字总数 71245
作品 0
南京
高级程序员
私信 提问
oracle迁移mysql总结

最近我处理了一个项目的数据库迁移,从oracle迁移至mysql,其中的一项主要工作就是对代码中的sql进行改写。这里针对两个库的不同点做一下总结,以备后查。 oracle与mysql之常用函数的区别: ...

SawyerZhou
2017/12/08
0
0
MyBatis返回 .0 问题的解决

MyBatis返回时间有小数点0,形如2018-10-10 10:00:00.0的解决方案--字段EXPIRED_DATE为例 数据库层面对应的时间字段都是定义为Date或者datetime这种时间类型 Oracle下 MySQL下 Java中间件中对...

karma123
2018/10/26
401
0
jsp向MySql中插入时间

在做jsp项目的时候遇到一个问题,就是把java.util.Date类型的数据插入到Mysql中的时候,如果数据库字段类型设置为dateTime类型,数据库中的时间就会变成“0000-00-00 00:00:00”,如果数据库...

学习的小猪
2014/04/04
0
0
C# 数据类型映射 (SQLite,MySQL,MSSQL,Oracle)

一、C# vs SQLite: C# SQLite 字段名 类型 库类型 GetFieldType(#) 转换 备注 F_BOOL bool BIT NOT NULL Boolean FBOOLNULL bool? BIT Boolean F_SBYTE sbyte INT8 NOT NULL SByte sbyte_ FS......

Yamazaki
2014/04/29
1
0
【MySQL】时间类型

【http://www.jb51.net/article/23966.htm】: 日期 日期类型 存储空间(byte) 日期格式 日期范围 datetime 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~9999-12-31 23:59:59 timestam......

Zoe_2016
2016/12/08
3
0

没有更多内容

加载失败,请刷新页面

加载更多

MainThreadSupport

MainThreadSupport EventBus 3.0 中的代码片段. org.greenrobot.eventbus.MainThreadSupport 定义一个接口,并给出默认实现类. 调用者可以在EventBus的构建者中替换该实现. public interface ...

马湖村第九后羿
28分钟前
2
0
指定要使用的形状来代替文字的显示

控制手机键盘弹出的功能只能在ios上实现,安卓是实现不了的,所以安卓只能使用type类型来控制键盘类型,例如你要弹出数字键盘就使用type="number",如果要弹出电话键盘就使用type="tel",但这...

前端老手
38分钟前
3
0
总结:Raft协议

一、Raft协议是什么? 分布式一致性算法。即解决分布式系统中各个副本数据一致性问题。 二、Raft的日志广播过程 发送日志到所有Followers(Raft中将非Leader节点称为Follower)。 Followers收...

浮躁的码农
45分钟前
3
0
Flask-admin Model View字段介绍

Model View字段介绍 can_create = True 是否可以创建can_edit = True 是否可以编辑can_delete = True 是否可以删除list_template = 'admin/model/list.html' 修改显......

dillonxiao
今天
5
0
从AnnotationTransactionAspect开始rushSpring事务

0. Spring 事务 with LTW 0.1. Spring 事务 With LTW的原因: Pure Proxy-base mode有缺陷,其失效原因分析及使用方法及运行机制(LoadTimeWeaverBeanDefinitionParser和 AspectJWeavingEnable......

Aruforce
今天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部