to_char和to_date返回不同​​的输出 [英] to_char and to_date are returning different output

问题描述

我正在使用以下查询.

select sysdate from dual where to_char(sysdate,'DD-MM-YY')='28-MAY-13';

，输出为空.但是对于以下查询，输出为当前日期.

and the output is null. But for below query, output is current date.

select sysdate from dual where to_date(sysdate,'DD-MM-YY')= to_date('28-MAY-13','DD-MM-YY');

为什么to_char函数不能返回sysdate?

Why the to_char function can not return the sysdate?

我们不应该使用to_char来比较日期吗?

Shouln't we use to_char to compare date?

推荐答案

尝试将"DD-MON-YY"格式映射为"28-MAY-13"

Try 'DD-MON-YY' format to map to '28-MAY-13'

'MM'映射到月份号.

'MM' maps to the month number.

SQL可以找出在to_date中使用正确的格式；当看到输入字符串为"28-MAY-13"格式时，它将自动将"DD-MM-YY"转换为"DD-MON-YY".但是，to_char不做任何假设；因此它正在尝试将"28-05-13"与"28-MAY-13"进行比较

SQL can figure out to use the correct format in to_date; it automatically converts 'DD-MM-YY' to 'DD-MON-YY' when it sees the input string is in '28-MAY-13' format. to_char does not make any assumptions, however; so it is trying to compare '28-05-13' to '28-MAY-13'

另外要注意的是DUAL只能返回一行；所以你可以做

An added note is that DUAL can only ever return one row; so you could just do

 SELECT sysdate FROM DUAL

Dan Bracuk在日期比较方面有一些好处；尽可能将它们保留为日期格式.如果只重要的是日期而不是时间，则使用trunc(DateTime)；如果您使用'='，这通常是必要的，但如果您检查BETWEEN

Dan Bracuk has some good points about date comparison; when possible keep them in date format. Use trunc(DateTime) if only the day matters and not the time; this is usually necessary if you use '=' but often not necessary if you do check for BETWEEN

这篇关于to_char和to_date返回不同​​的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！