Oracle SQL查询日期格式 [英] Oracle SQL query for Date format

问题描述

我总是对ORACLE SQL查询中的日期格式感到困惑，并花几分钟与Google在一起，当我们在数据库表中使用不同日期格式时，有人可以向我解释最简单的方法吗?

I always get confused with date format in ORACLE SQL query and spend minutes together to google, Can someone explain me the simplest way to tackle when we have different format of date in database table ?

我有一个日期列为ES_DATE，保存的数据类型为TIMESTAMP(6)和LOCAL TIME ZONE，日期为27-APR-12 11.52.48.294030000 AM.

for instance i have a date column as ES_DATE, holds data as 27-APR-12 11.52.48.294030000 AM of Data type TIMESTAMP(6) WITH LOCAL TIME ZONE.

我编写了简单的选择查询来获取该特定日期的数据，但它什么也没有返回.有人可以向我解释吗?

I wrote simple select query to fetch data for that particular day and it returns me nothing. Can someone explain me ?

select * from table
where es_date=TO_DATE('27-APR-12','dd-MON-yy')

或

select * from table where es_date = '27-APR-12';

推荐答案

to_date()返回00:00:00的日期，因此您需要删除"要比较的日期的分钟数:

to_date() returns a date at 00:00:00, so you need to "remove" the minutes from the date you are comparing to:

select * 
from table
where trunc(es_date) = TO_DATE('27-APR-12','dd-MON-yy')

如果您经常这样做，则可能要在trunc(es_date)上创建索引.

You probably want to create an index on trunc(es_date) if that is something you are doing on a regular basis.

如果将默认日期格式更改为任何其他内容，则文字'27-APR-12'可能很容易失败.因此，请确保您始终将to_date()与正确的格式掩码(或ANSI文字:date '2012-04-27')一起使用

The literal '27-APR-12' can fail very easily if the default date format is changed to anything different. So make sure you you always use to_date() with a proper format mask (or an ANSI literal: date '2012-04-27')

尽管您正确使用了to_date()并且不依赖于隐式数据类型转换，但是由于格式为'dd-MON-yy'，因此to_date()的用法仍然存在一些隐患.

Although you did right in using to_date() and not relying on implict data type conversion, your usage of to_date() still has a subtle pitfall because of the format 'dd-MON-yy'.

使用其他语言设置时，这很容易失败，例如TO_DATE('27-MAY-12','dd-MON-yy')当NLS_LANG设置为德语时.避免使用其他语言格式可能不同的任何内容.仅使用四位数的年份，例如'dd-mm-yyyy'或'yyyy-mm-dd'

With a different language setting this might easily fail e.g. TO_DATE('27-MAY-12','dd-MON-yy') when NLS_LANG is set to german. Avoid anything in the format that might be different in a different language. Using a four digit year and only numbers e.g. 'dd-mm-yyyy' or 'yyyy-mm-dd'

这篇关于Oracle SQL查询日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！