简单的 Oracle SQL 日期语法问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/299770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simple Oracle SQL date syntax question
提问by Joe
I am trying to convert a working MS Access query to run on an Oracle database being accessed via VB Script (.asp). This is the last section of the WHERE clause:
我正在尝试将有效的 MS Access 查询转换为在通过 VB 脚本 (.asp) 访问的 Oracle 数据库上运行。这是 WHERE 子句的最后一部分：
sql = sql & "WHERE (UAT.HB.MB_MODE = 'A' AND UAT.HB.PRINT_DATE >= '"
& SD & "' AND UAT.HB.PRINT_DATE <= '" & ED &"' )"
The variable "SD" (i.e. "start date") is a text string that can contain a value such as "11/11/2008". The same goes for the variable "ED" (i.e. "end date").
变量“SD”（即“开始日期”）是一个文本字符串，可以包含诸如“11/11/2008”之类的值。变量“ED”（即“结束日期”）也是如此。
However, the dates do not work. Does Oracle require a special way to use dates?
但是，日期不起作用。Oracle 是否需要一种特殊的方式来使用日期？
Do the dates have to be converted? Do I surround them with the '#' keyword like you would in MS Access?
日期必须转换吗？我是否像在 MS Access 中那样用“#”关键字将它们括起来？
回答by Bill Karwin
回答by gpojd
Don't assume the default Oracle date format is anything. Check the NLS_DATE_FORMATor use TO_DATEto convert it. Like this:
不要假设默认的 Oracle 日期格式是任何东西。检查NLS_DATE_FORMAT或使用TO_DATE进行转换。像这样：
TO_DATE('2008-11-18 14:13:59', 'YYYY-MM-DD HH24:Mi:SS')
Note the 'Mi' for the minutes and not 'MM'. That catches a lot of people.
注意分钟的“Mi”而不是“MM”。这吸引了很多人。
回答by Vincent Ramdhanie
回答by Brett McCann
The default date format for Oracle is "dd-mon-yy". You can do a TO_CHAR function on the date field to convert it to a format you prefer to match on.
Oracle 的默认日期格式是“dd-mon-yy”。您可以在日期字段上执行 TO_CHAR 函数以将其转换为您喜欢匹配的格式。
回答by rich
Here's a couple examples for converting to and from dates:
以下是转换日期和日期的几个示例：
select to_date('2008/11/18:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') from dualselect to_char(sysdate, 'mm/dd/yyyy') from dual
select to_date('2008/11/18:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') from dualselect to_char(sysdate, 'mm/dd/yyyy') from dual
回答by Shane
The default Oracle date format can change from one database to the next. So if you don't know the date format your database is using then you should use the TO_DATE function with an explicit date format string. Given the fact that the default date format could change at any time, using an explicit date format string is the best thing to do anyway.
默认的 Oracle 日期格式可以从一个数据库更改为下一个数据库。因此，如果您不知道数据库使用的日期格式，那么您应该使用带有显式日期格式字符串的 TO_DATE 函数。鉴于默认日期格式可能随时更改，使用显式日期格式字符串是最好的选择。
ex: TO_DATE('11/11/2008 17:30','MM/DD/YYYY HH24:MI')
例如：TO_DATE('11/11/2008 17:30','MM/DD/YYYY HH24:MI')
回答by hamishmcn
Don't forget that the date includes a time (defaults to 00:00:00), so
don't get caught out by something like this:
given three dates, start_date, print_date, end_date, all on the samedayprint_date >= start_date AND print_date <= end_datefails because print_datewas greater than end_date:start_datewas 2008-11-19 (00:00:00)
and end_datewas 2008-11-19 (00:00:00)
and print_datewas 2008-11-19 (16:00:00)
不要忘记日期包括一个时间（默认为 00:00:00），所以
不要被这样的事情所困扰：
给定三个日期，start_date, print_date, end_date在同一天都print_date >= start_date AND print_date <= end_date失败，因为print_date大于end_date：start_date是 2008 -11-19 (00:00:00)
和end_date2008-11-19 (00:00:00)
和print_date2008-11-19 ( 16:00:00)
