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

问题描述

我总是对 ORACLE SQL 查询中的日期格式感到困惑，花几分钟一起去谷歌，有人能解释一下当我们在数据库表中有不同格式的日期时最简单的解决方法吗?

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，将数据保存为 27-APR-12 11.52.48.294030000 AM，数据类型为 TIMESTAMP(6) WITH LOCAL TIME ZONE.

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' 很容易失败.因此，请确保始终使用带有正确格式掩码(或 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，您对 to_date() 的使用仍然有一个微妙的陷阱-yy'.

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屋！