TO_CHAR和TO_DATE给出不同的结果.如何使用TO_DATE实现TO_CHAR功能? [英] TO_CHAR and TO_DATE giving different results.How to achieve the TO_CHAR functionality using TO_DATE?

问题描述

SELECT TO_CHAR((select logical_date -1 from logical_date 
where logical_date_type='B'),'DD/MM/YYYY HH24:MI:SS') FROM DUAL;

此查询返回23/04/2016 00:00:00

o/p of select logical_date -1 from logical_date where logical_date_type='B' :4/23/2016

SELECT TO_DATE((select logical_date -1 from logical_date 
where logical_date_type='B'),'DD/MM/YYYY HH24:MI:SS') FROM DUAL;

此查询返回4/23/0016.

如何获取使用TO_DATE的TO_CHAR给出的格式?

How do I get the format given by TO_CHAR using TO_DATE ??

推荐答案

日期没有任何固有格式. Oracle有一个内部表示形式，它看起来与您认为是日期的任何东西都不一样.客户端或应用程序决定如何显示日期(例如，使用NLS_DATE_FORMAT)，也可以像在第一个示例中一样，使用TO_CHAR将日期转换为具有指定格式的字符串.

Dates do not have any intrinsic format. Oracle has an internal representation which doesn't look like anything you'd recognise as a date. The client or application decides how to display the date (e.g. using NLS_DATE_FORMAT), or you can use TO_CHAR to convert the date to a string with a format you specify, as you're doing in your first example.

在第二个示例中，您正在使用NLS设置将您的实际日期隐式转换为字符串-在此过程中损失了世纪，因此大概是YY而不是YYYY-然后将其转换回日期.我很惊讶这不会出错，因为您正在交换月和日头寸.

In your second example you are doing an implicit comversion of your actual date to a string, using your NLS setting - losing the century in the process, so presumably that has YY rather than YYYY - and then you convert that back to a date. I'm surprised that doesn't error as you are swapping the month and day positions.

不要那样做.即使您的设置不会在途中丢失任何信息，转换为字符串并返回日期也是没有意义的.

Do not do that. Converting to a string and back to a date is pointless, even if your settings don't lose information on the way.

如果您希望将其作为客户端或其他进程使用的日期，请执行以下操作:

If you want it as a date for a client or other process to use just do:

select logical_date -1
from logical_date 
where logical_date_type='B'

如果要与其他日期进行比较，则仍将其保留为日期:

If you want to compare against another date then still leave it as a date:

select other_columns
from logical_date 
where logical_date_type='B'
and logical_date -1 < sysdate

如果您要指定显示格式用于显示，请使用:

If you want to specify the format for display then use:

select to_char(logical_date -1, 'DD/MM/YYYY')
from logical_date 
where logical_date_type='B'

这篇关于TO_CHAR和TO_DATE给出不同的结果.如何使用TO_DATE实现TO_CHAR功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！