定义oracle数据库的日期格式
我正在尝试执行以下 SQL：
INSERT INTO "x"
("x_id", "requested_function", "user_action", "t_users_id", "date", "wf_process", "details")
VALUES
('66', 'index', 'view', '1', '2011-02-04 22:14:19', '15', '');
..但我收到此错误：ORA-01861：文字与格式字符串不匹配。
如果我像这样添加日期格式，这个错误就解决了：
INSERT INTO "x"
("x_id", "requested_function", "user_action", "t_users_id", "date", "wf_process", "details")
VALUES
('66', 'index', 'view', '1',to_date('2011-02-04 22:14:19','yyyy-mm-dd hh24:mi:ss'), '15', '');
我不想为每个SQL语句定义日期格式，我希望这种格式成为数据库中所有日期字段的标准格式，而不需要定义它，在换句话说，第一个 SQL 语句必须正确运行，没有任何错误。
我可以为oracle数据库定义日期格式，这样就不需要为每个SQL语句再次定义它吗？
I'm trying to execute the following SQL:
INSERT INTO "x"
("x_id", "requested_function", "user_action", "t_users_id", "date", "wf_process", "details")
VALUES
('66', 'index', 'view', '1', '2011-02-04 22:14:19', '15', '');
..but i get this error : ORA-01861: literal does not match format string.
This error solved if i add the date format like this:
INSERT INTO "x"
("x_id", "requested_function", "user_action", "t_users_id", "date", "wf_process", "details")
VALUES
('66', 'index', 'view', '1',to_date('2011-02-04 22:14:19','yyyy-mm-dd hh24:mi:ss'), '15', '');
I don't want to define the date format for each SQL statement, I want this format to be standard for all date fields in the database, without the need to define it,in other word the first SQL statement must run correctly without any errors.
Can I define the date format for oracle database, so no need to define it again with each SQL statement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是思考编程的错误方式。隐式的不好，显式的好。如果您只是将字符串放入数据列中并希望默认值按您的预期工作，则很可能会产生错误和性能问题。
但我绝不会妨碍得到问题的答案。
更改 NLS_DATE_FORMAT 初始化参数。
That is the wrong way to think about programming. Implicit is bad, explicit is good. If you just toss a string into a data column and hope the default works as you expect, you're very likely to create bugs and performance issues.
But far be it from me to get in the way of getting the answer to the question.
Change the NLS_DATE_FORMAT initialization parameter.
这是非常糟糕的 SQL。两个问题：1）Oracle将无法优化（每次运行查询时都必须解析），使得数据库操作变慢； 2) 您容易受到 SQL 注入攻击。
最好将参数化查询与准备好的语句一起使用，这将解决上述问题以及日期格式问题。
编辑：要直接回答你的问题，如果你真的不想改用准备好的语句（你已经被警告过！），那么你可以做这样的事情：
但同样，这是一个非常由于上述原因，这是个坏主意
That is pretty bad SQL. Two problems: 1) Oracle will not be able to optimize (will have to parse each time you run a query), making database operations slow; and 2) You are open to SQL injection attacks.
It's far better to use parametrized queries with prepared statements, which will solve both issues above and your date formatting problem as well.
EDIT: To directly answer your question, and if you really do not want to change over to prepared statements (you have been warned!), then you can do somethign like this:
But again, it's a very bad idea for the reasons stated above
日期和时间文字的 SQL92 标准就是此处的方法。
使用它的一个例子是：
The SQL92 standard for date and time literals is the way to go here.
An example of using it would be: