在 oracle 和 mysql 中使用日期格式
大家好
编写与 mysql、oracle 和 mssql 兼容的 SQL 语句的最佳方法是什么 数据库。
例如，当我尝试执行这个：
INSERT INTO "table_name ("date") VALUES (to_date('2011-02-11 16:48:08','yyyy-mm-dd hh24:mi:ss'));
它在oracle中工作，但是这个查询会给出错误：当我尝试在MYSQL数据库中执行它时，to_date不存在。
但是下面的SQL在mysql上工作，而不是在mysql上工作在oracle中工作：
INSERT INTO t_sys_log (date) VALUES ('2011-11-11');
那么你能帮我解决mysql和oracle中的日期兼容性问题，并告诉我是否有办法使标准SQL语句在rracle、mssql和mysql中工作？ 谢谢
Hi All
what is the best way to write SQL statement that is compatible with mysql,oracle,and mssql
database.
for example when i try to execute this:
INSERT INTO "table_name ("date") VALUES (to_date('2011-02-11 16:48:08','yyyy-mm-dd hh24:mi:ss'));
it works in oracle,but this query will give error: to_date is not exist when i try to execute it in MYSQL database.
but this follwoing SQL works on mysql and not work in oracle:
INSERT INTO t_sys_log (date) VALUES ('2011-11-11');
So can you please help me to solve date problem compatibility in mysql and oracle and tell me if there is a way to make standard SQL statement works in rracle,mssql, and mysql ?
Thank You
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尝试使用 ANSI 标准 DATE 文字
Try the ANSI standard DATE literal
是的，to_date 不适用于这两个数据库，因为它是仅由 Oracle 提供的 SQL 扩展。
你走得越深，它就越具有挑战性。日期与时间戳并不完全相同，因此您会发现自己陷入困境，您希望在某些数据库中使用时间戳，而在其他数据库中使用日期。这是因为有些数据库中的“日期”仅包含日历日期，而不包含任何关联的小时、分钟和秒。
MSSQL 日期也受平台时区规则的约束，而其他数据库不以相同方式保存受时区规则约束的日期。
最后，如果你真的想让它正常工作，你需要编写一个软件层，将日期转换为底层数据库所需的格式，放弃“一条 SQL 语句处理所有日期”。
Yes, to_date will not be available to both databases, as it is a SQL extension which was only offered by Oracle.
It gets more challenging the deeper you go. Date is not quite the same thing as Timestamp, so you'll find yourself stuck where you'll want Timestamps in some databases and Dates in others. This is because there are databases where Date only contains a calendar date, and not any associated hours, minutes, and seconds.
MSSQL dates are also subject to the platform's timezone rules, while other databases don't hold dates subject to timezone rules in the same manner.
In the end, if you really want it to work correctly, you need to write a software layer which translates dates into the formats required by the underlying database, giving up on the "one SQL statement to do all dates".
我遇到了同样的问题。不幸的是我找不到一个好的方法来做到这一点。相反，我创建了一个可插入的“帮助程序”类（这是用 Java 编写的），它与数据库驱动程序一起设置。它有方法来创建 DBMS 特定的 SQL 语句部分。
I ran into that same problem. Unfortunately I couldn't find a good way to do it. Instead I created a plugable "helper" class (this was in Java), that was set along with the database driver. It had methods to create parts of the SQL statement that were DBMS specific.