SQL日期格式转换 [英] SQL Date Format Conversion
问题描述
我对SQL日期有疑问. 我正在使用的表具有以下格式的日期字段:"22-SEP-08".该字段是日期列.
I have a question regarding SQL dates. The table I am working with has a date field in the following format: "22-SEP-08". The field is a date column.
我试图弄清楚如何输出从2000年1月1日到今天的记录.
I am trying to figure out how to output records from 1/1/2000 to present day.
以下代码未过滤日期字段:
The code below is not filtering the date field:
Select distinct entity.lt_date
from feed.entitytable entity
where entity.lt_date >= '2000-01-01'
非常感谢您提供有关此问题的任何帮助.谢谢！
Any help regarding this issue is much appreciated. Thanks!
我正在使用Oracle SQL Developer编写代码.
I am using Oracle SQL Developer to write my code.
推荐答案
日期确实 不是 具有格式".您看到的任何格式都会由应用程序显示日期值来应用.
DATEs do not have "a format". Any format you see is applied by the application displaying the date value.
您可以更改SQL Developer的配置以显示其他格式的日期，也可以使用to_char()以所需的方式格式化日期.
You can either change the configuration of SQL Developer to display dates in a different format, or you can use to_char() to format the date the way you want.
您的语句不起作用的原因很可能是由于您所依赖的隐式数据类型转换.
The reason your statement does not work, is most probably because of the implicit data type conversion that you are relying on.
'2000-01-01'是字符串值，而不是日期.然后使用会话的NLS设置转换字符串.鉴于您看到日期显示为DD-MON-YY的事实，这意味着邪恶的隐式数据类型转换将使用该格式.您应该始终提供日期值作为实际日期文字.
'2000-01-01' is a string value, not a date. And the string is converted using the NLS settings of your session. Given the fact that you see dates displayed as DD-MON-YY means that that is the format that is used by the evil implicit data type conversion. You should supply date values always as real date literals.
有两种指定 real 日期文字的方式.第一个是ANSI SQL，它在ISO格式的字符串前简单地使用关键字DATE:
There are two ways of specifying a real date literal. The first is ANSI SQL and simple uses the keyword DATE in front of an ISO formatted string:
where entity.lt_date >= DATE '2000-01-01'
请注意在字符串前面的DATE关键字，这使其成为实际日期文字而不是字符串表达式.
Note the DATE keyword in front of the string, wich makes it a real date literal not a string expression.
另一种选择是使用to_date()将字符值转换为日期:
The other option is to use to_date() to convert a character value into a date:
where entity.lt_date >= to_date('2000-01-01', 'yyyy-mm-dd');
有关指定日期文字的更多详细信息，可以在手册中找到:
More details about specifying date literals can be found in the manual:
- Date literals
- to_date function
这篇关于SQL日期格式转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
