在MySQL插入语句中将Oracle转换为MySQL时间戳 [英] Oracle to MySQL timestamp conversion in MySQL insert statement

问题描述

我已经从Oracle 11g实例中将数据导出为插入语句.他们需要进入一个MySQL 5.5实例.在 TIMESTAMP 转换时遇到麻烦；我知道我可以在MySQL中的 INSERT 语句中内联使用 TO_TIMESTAMP 函数；但是，不能确定要使用的正确标志.如下所示，按线性顺序是:Oracle timestamp语句和我对MySQL兼容语句的尝试.显然，它不起作用(因为MySQL指出存在语法错误).

I've already exported data from an Oracle 11g instance as insert statements. They need to go into a MySQL 5.5 instance. Having trouble with the TIMESTAMP conversion; I know I can use the TO_TIMESTAMP function inline with INSERT statements in MySQL; however, am unsure as to the correct flags to use. As follows below, in linear order, is: the Oracle timestamp statement and my attempt at a MySQL compatible statement. Obviously it's not working (in that MySQL states there's a syntax error).

Oracle:

TO_TIMESTAMP('12/22/2015 5:08:59.245837 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM')

MySQL:

TO_TIMESTAMP('12/22/2015 5:08:59.245837 PM','%m/%d/%Y %h:%i:%s')

在微秒和AM/PM的标志之外，我还缺少什么?

What am I missing beyond flags for microseconds and AM/PM?

推荐答案

如果您希望oracle的日期和时间戳与MySql兼容，只需更改您的会话

if you want the date and the timestamp of oracle to be compatible with MySql just alter you session

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'

最好更改会话

这篇关于在MySQL插入语句中将Oracle转换为MySQL时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！