TO_CHAR и TO_DATE дают разные результаты. Как реализовать функциональность TO_CHAR с помощью 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_CHAR, используя TO_DATE ??


sql oracle to-char date-formatting to-date
person Rishi Deorukhkar    schedule 10.02.2016    source источник

Ответы (3)


arrow_upward
6
arrow_downward

Даты не имеют внутреннего формата. У Oracle есть внутреннее представление, которое не похоже ни на что, что вы могли бы распознать как дату. Клиент или приложение решают, как отображать дату (например, используя NLS_DATE_FORMAT), или вы можете использовать TO_CHAR для преобразования даты в строку с указанным вами форматом, как вы делаете в своем первом примере.

Во втором примере вы выполняете неявное преобразование своей фактической даты в строку, используя настройку NLS - теряя столетие в процессе, поэтому, предположительно, это YY, а не YYYY, - а затем вы конвертируете это обратно в дату. Я удивлен, что это не ошибка, когда вы меняете местами месяц и день.

Не делай этого. Преобразование в строку и обратно в дату бессмысленно, даже если ваши настройки не потеряют информацию по пути.

Если вы хотите, чтобы это была дата для клиента или другого процесса, просто выполните:

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

Если вы хотите сравнить с другой датой, оставьте ее как дату:

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

Если вы хотите указать формат для отображения, используйте:

select to_char(logical_date -1, 'DD/MM/YYYY')
from logical_date 
where logical_date_type='B'
person Alex Poole    schedule 10.02.2016
comment
На самом деле сохраненные байты по-прежнему доступны для чтения: ixora.com.au/notes/date_representation.htm< /а>. - person Husqvik; 10.02.2016
comment
Я хочу сравнить его с sysdate ('DD/MM/YYYY HH24:MI:SS') в предложении where, так как мне реализовать его с помощью TO_DATE() вместо TO_CHAR() - person Rishi Deorukhkar; 10.02.2016
comment
@Husqvik - я не говорил, что это нечитаемо, просто ОП не распознает это как дату. Я не думал вдаваться в подробности о том, как увидеть это через дамп или как интерпретировать, что поможет ОП на этом этапе * 8-) - person Alex Poole; 10.02.2016
comment
@Rishi - обе даты, так что сравните их как даты. SYSDATE отображается в этом формате вашим клиентом. Не преобразовывайте ни один из них в строки. - person Alex Poole; 10.02.2016
comment
@Husqvik Весь смысл маски формата состоит в том, чтобы сделать байтовые данные читаемыми, поскольку человек, не являющийся оракулом, не поймет внутренний формат :-) - person Lalit Kumar B; 10.02.2016

arrow_upward
3
arrow_downward

Проблема связана с форматом даты по умолчанию для вашего сеанса, который настроен в настройках вашего клиента оракула. ​​Чтобы проверить настройки NLS_DATE_FORMAT для вашего сеанса

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

вот как вы можете изменить этот параметр для своего сеанса, чтобы достичь желаемых результатов:

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
person Ricardo Arnold    schedule 10.02.2016
comment
Это предполагает, что OP всегда сможет контролировать среду, в которой будет выполняться запрос; на самом деле лучше вообще не полагаться на настройку NLS. Это также заставляет их выполнять ненужное преобразование в строку и обратно во втором примере. - person Alex Poole; 10.02.2016
comment
Привет @AlexPoole, я согласен ... Предполагая, что вы пишете код приложения, вам следует не полагаться на настройки NLS_DATE_FORMAT и использовать TO_CHAR. - person Ricardo Arnold; 10.02.2016

arrow_upward
3
arrow_downward

Как получить формат, заданный TO_CHAR, используя TO_DATE?

Во-первых, DATE не имеет никакого формата. Oracle не хранит даты в формате, который вы видите. Он хранит его внутри в 7 байтах, причем каждый байт хранит разные компоненты значения datetime.

Byte    Description
----    -------------------------------------------------
1       Century value but before storing it add 100 to it
2       Year and 100 is added to it before storing
3       Month
4       Day of the month
5       Hours but add 1 before storing it
6       Minutes but add 1 before storing it
7       Seconds but add 1 before storing it

Формат предназначен только для отображения. TO_DATE используется для преобразования литерала в дату и не имеет ничего общего с форматированием. Чтобы отобразить дату в желаемом формате, используйте TO_CHAR с соответствующей FORMAT MODEL.

Кроме того, помните, форматирование имеет порядок приоритета:

Давайте рассмотрим хронологический порядок старшинства, т. е. начиная с высшего к наименьшему:

  • Использование TO_CHAR или TO_DATE в отдельной инструкции SQL

  • ALTER SESSION SET NLS_DATE_FORMAT=’whatever format model you want’;

  • Установка его в качестве переменной среды ОС на клиентском компьютере

  • Параметр NLS_DATE_FORMAT находится в параметрах инициализации базы данных.

Например,

Отдельный оператор SQL:

SQL> SELECT HIREDATE, TO_CHAR(hiredate, 'YYYY-MM-DD') FROM emp;

HIREDATE            TO_CHAR(HI
------------------- ----------
17/12/1980 00:00:00 1980-12-17
20/02/1981 00:00:00 1981-02-20
22/02/1981 00:00:00 1981-02-22
02/04/1981 00:00:00 1981-04-02
28/09/1981 00:00:00 1981-09-28
01/05/1981 00:00:00 1981-05-01
09/06/1981 00:00:00 1981-06-09
09/12/1982 00:00:00 1982-12-09
17/11/1981 00:00:00 1981-11-17
08/09/1981 00:00:00 1981-09-08
12/01/1983 00:00:00 1983-01-12
03/12/1981 00:00:00 1981-12-03
03/12/1981 00:00:00 1981-12-03
23/01/1982 00:00:00 1982-01-23

14 rows selected.

Уровень сеанса:

SQL> alter session set nls_date_format='YYYY-MM-DD';

Session altered.

SQL> SELECT hiredate FROM emp;

HIREDATE
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
1981-06-09
1982-12-09
1981-11-17
1981-09-08
1983-01-12
1981-12-03
1981-12-03
1982-01-23

14 rows selected.

SQL>

TO_DATE((выбрать логическую_дату

Это не верно.

Никогда не применяйте TO_DATE к столбцу DATE. Это заставляет Oracle:

  • сначала преобразовать его в строку
  • затем преобразовать его обратно в дату

на основе настроек NLS для конкретной локали. Вам нужно TO_DATE, чтобы преобразовать литерал в дату. Для арифметики даты оставьте дату как есть.

person Lalit Kumar B    schedule 10.02.2016