Как неявно конвертируется тип данных DATE в NUMBER?
Оно вернёт значение: 2459060 Никак не пойму, откуда берётся это число? Так как, SYSDATE-SYSDATE это число, но дату же нельзя так просто преобразовать в число:
TO_NUMBER(DATE'2020-07-29') ORA-01722: invalid number В документации SQL Language Reference NVL2 сказано, что если выражение второго аргумента число, то последующий аргумент будет неявно преобразован к числу:
If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
Поэтому вопрос, какая конвертация используется неявно, чтобы тип данных DATE преобразовать в NUMBER ? Свободный перевод вопроса How does Oracle SQL convert DATE to NUMBER implicitly? от участника @Nicholas Koldys
Отслеживать
задан 29 июл 2020 в 19:56
51.6k 201 201 золотой знак 63 63 серебряных знака 245 245 бронзовых знаков
ассоциация:stackoverflow.com/q/61992011
31 июл 2020 в 13:15
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
По факту выполняется это:
select to_number (to_char (date'2020-07-29','J')) days from dual; DAYS ---------- 2459060 Где J , это число дней прошедших с 1 января 4712 до н.э., то есть то значение, которое возвращает выражение в вопросе.
Не совсем очевидно, что это должно быть так, но это так. Если второй аргумент задать явно числом, то получим ошибку:
select nvl2 (null, 99, date'2020-05-29') from dual; Error report - SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE Если сдедлать дамп результата вычитания дат, то он возвращает не число (Typ=2), а другой, внутренний и недокументированный, насколько я вижу, тип данных:
select dump (sysdate-sysdate) dump from dual; DUMP -------------------------------- Typ=14 Len=8: 0,0,0,0,0,0,0,0 Очевидно, что это приводит к тому, что третий аргумент приводится к тому же самому типу, к которому он наиболее эквивалентен:
select date'2020-07-29'-date'-4712-01-01' days from dual; DAYS ---------- 2459059 Поэтому, выглядит так, что либо делается что-то похожее на последнее, но адаптироанное под этот тип данных, либо делается внутреннее преобразование к формату J , либо что-то ещё другое. Повидимому, это поведение не документированно.
Свободный перевод ответа от участника @Alex Poole
Отслеживать
ответ дан 29 июл 2020 в 19:56
51.6k 201 201 золотой знак 63 63 серебряных знака 245 245 бронзовых знаков
Здесь немного подробнее о дате 1 января 4712 до н.э. — «Oracle database internally stores date in Julian calendar format, Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). For example, today i.e, 23-AUG-2015 is the 2457258th day from 01-JAN-4713 BC of Julian calendar.»
29 июл 2020 в 20:04
Спасибо @MaxU! Это скорее всего будет тема моего авторского ответа ;))
29 июл 2020 в 20:10
Тип результата выражения (date2-date1) не является числом. К численному типу NUMBER он будет приведён при присвоении значения переменной или колонке таблицы. До того, это внутренняя (недокументированная) си структура данных с типом ( Typ=14 ):
var rc refcursor declare dd number := sysdate-(sysdate-125.125); begin open :rc for select dump (sysdate-(sysdate-125.125), 16) dump, dump (dd, 16) dump, dd ddiff from dual; end; / DUMP DUMP DDIFF -------------------------------- -------------------------------- ---------- Typ=14 Len=8: 7d,0,0,0,30,2a,0,0 Typ=2 Len=5: c2,2,1a,d,33 125.125 Где, длина 8 байт, 4 байта — кол-во дней, 3 байта кол-во секунд, последний байт резервирован. На архитектуре с little endian можно посчитать справa налево:
select to_number ('7d', 'xx') days, to_number ('2a30', 'xxxx')/(60*60) hours from dual / DAYS HOURS ---------- ---------- 125 3 То есть, по сути это интервал времени. Выглядит логично, привести дату к интервалу времени используя Юлианскую дату (Julian Days) как вычитаемое:
select (date'2020-07-29')-date'-4712-01-01'+1 days from dual; DAYS ---------- 2459060 Кроме того, результат вычитания дат, т.е. эту внутреннюю структуру данных, можно привести к стандартному типу данных INTERVAL , что с подстановкой просто численного литерала даст ошибку:
select (sysdate-(sysdate-125.125)) day (3) to second interval from dual; INTERVAL -------------------- +125 03:00:00.000000 select (125.125) day (3) to second interval from dual; Error report - SQL Error: ORA-30083: syntax error was found in interval value expression SQL. Как привести дату в строку в нужный формат в SQL Server
На выходе получается дату в строковом виде в нужном формате.
Что еще посмотреть по SQL Server
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Функция DATE_FORMAT
Функция DATE_FORMAT преобразует дату из формата год-месяц-день или формата год-месяц-день часы:минуты:секунды в другой удобный нам формат.
Синтаксис
SELECT DATE_FORMAT(дата, формат) FROM имя_таблицы WHERE условие
Команды
| Команда | Описание |
|---|---|
| %d | День месяца, число от 00 до 31 . |
| %e | День месяца, число от 0 до 31 . |
| %m | Месяц, число от 01 до 12 . |
| %c | Месяц, число от 1 до 12 . |
| %Y | Год, число, 4 цифры. |
| %y | Год, число, 2 цифры. |
| %j | День года, число от 001 до 366 . |
| %H | Час, число от 00 до 23 . |
| %k | Час, число от 0 до 23 . |
| %h | Час, число от 01 до 12 . |
| %I | Час, число от 01 до 12 . |
| %l | Час, число от 1 до 12 . |
| %i | Минуты, число от 00 до 59 . |
| %S | Секунды, число от 00 до 59 . |
| %s | Секунды, число от 00 до 59 . |
| %w | День недели (0 — воскресенье, 1 — понедельник). |
| %W | Название дня недели по-английски. |
| %a | Сокращенный день недели по-английски. |
| %M | Название месяца по-английски. |
| %b | Сокращенный месяц по-английски. |
| %D | День месяца с английским суффиксом (1st, 2nd, 3rd и т.д.). |
| %r | Время, 12 -часовой формат (hh:mm:ss [AP]M). |
| %T | Время, 24 -часовой формат (hh:mm:ss). |
| %p | AM или PM. |
| %U | Неделя, где воскресенье считается первым днем недели, число от 00 до 53 . |
| %u | Неделя, где понедельник считается первым днем недели, число от 00 до 53 . |
| %V | Неделя, где воскресенье считается первым днем недели, число от 01 до 53 . Используется с `%X’. |
| %v | Неделя, где понедельник считается первым днем недели, число от 01 до 53 . Используется с `%x’. |
| %X | Год для недели, где воскресенье считается первым днем недели, число, 4 цифры. Используется с ‘%V’ . |
| %x | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда. Используется с ‘%v’ . |
| %% | Символ `%’. |
Таблицы для примеров
| id айди |
name имя |
date дата регистрации |
|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 |
Пример
В данном примере при выборке создается новое поле, в котором будет лежать дата в другом формате:
SELECT *, DATE_FORMAT(date, ‘%d.%m.%Y’) as new_date FROM employees
Результат выполнения кода:
| id айди |
name имя |
date дата регистрации |
new_date дата в новом формате |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 01.03.2010 |
| 2 | user2 | 2011-04-02 13:02:03 | 02.04.2011 |
| 3 | user3 | 2012-05-03 14:03:04 | 03.05.2012 |
Смотрите также
- функцию TIME_FORMAT ,
которая меняет формат вывода времени
2.21. Конвертирование типов
Язык SQL является интерпретируемым. В отличие от компилируемого языка, где программа с помощью компилятора переводиться в машинный код, интерпретируемый язык остается в виде своего исходного кода и так же выполняется сервером. Когда мы запрашиваем выполнения SQL сценария, то сервер интерпретирует SQL команды и выполняет необходимые действия.
Большинство интерпретируемых языков не типизированы и автоматически конвертируют данные из одного формата в другой. Язык Transact-SQL привязан к типам, и вы должны явно преобразовывать форматы там, где это необходимо.
Для преобразования данных используются команда (функция) CAST и CONVERT. В общем, виде CAST выглядит следующим образом:
CAST ( expression AS data_type )
Рассмотрим классический пример. Допустим, что нам необходимо сложить два числовых поля. Для этого возьмем из таблицы tbPeoples поля «idPeoples» и «idPosition». Попробуем выполнить следующий запрос:
SELECT idPeoples+idPosition FROM tbPeoples
В результате мы получим таблицу с результатами арифметического сложения этих полей. А если нужно сложить эти колонки как строки? То есть ‘1’+’1′ в результате должно дать ’11’, а не арифметическую сумму (число 2). Как решить эту проблему? Нужно просто привести значения этих колонок к строке:
SELECT cast(idPeoples as varchar(10)) + cast(idPosition as varchar(10)) FROM tbPeoples
Что здесь происходит? Каждое поле мы приводим к строковому типу varchar размером в 10 символов. Например, первое поле преобразуется к строке следующим образом:
cast(idPeoples as varchar(10))
После команды cast в круглых скобках сначала указываем имя поля, которое нужно привести, а после ключевого слова as необходимо указать новый тип для поля. Если это строка, то необходимо указать и ее размер.
Открою небольшой секрет. Автоматическое преобразование в SQL сервере все же есть. Как вы думаете, что будет, если выполнить следующий запрос:
SELECT cast(idPeoples as varchar(10)) + idPosition FROM tbPeoples
В этом примере первое поле приводиться к строке, а второе остается числом. Что будет результатом сложения строки с числом? Если вы попробуете выполнить этот запрос, то увидите, что результатом будет арифметическое сложение двух чисел. Не смотря на то, что первую колонку мы привели к строке, во время выполнения запроса SQL сервер автоматически вернул этому полю числовой тип и сложил колонки как числа. Таким образом, автоматическое преобразование есть, но автоматически можно привести далеко не все типы.
Я рекомендую вам не надеяться на автоматизм сервера и интерпретатора, а указывать необходимые типы явно. Я уже видел достаточно много запросов, в которых ошибка крылась именно в разном понимании преобразования. Программист считает, что сервер должен выбрать один тип, а сервер выбирает другой и результат запроса получается не таким, как ожидает программист.
Во время преобразования нужно быть внимательным и при указании типа и размера. Например, следующий запрос приводит слово ‘Привет’ к типу char размером в 10 символов. Для наглядности я прибавляю к результату содержимое поля «vcFamil», где у нас храниться фамилия работника:
SELECT CAST('Привет ' AS char(10)) + vcFamil FROM tbPeoples
Результат может быть неожиданным. Например:
Привет mr.ИВАНОВ
Обратите внимание, что между словом ‘Привет’ и содержимым поля несколько пробелов, а ведь мы их не указывали. Это связано с правилами использования типа char. Дело в том, что переменные или поля этого типа полностью занимают отведенное пространство. Если переменная требует меньшее пространство (как слово ‘Привет’, требует только 6 символов), то оставшееся пространство заполняется пробелами. Преобразуемое слово занимает 6 символов, а мы его приводим к типу char из 10 символов. Это значит, что после в конце строки будет добавлено 4 пробела, чтобы строка занимала все отведенное пространство.
Теперь посмотрим на команду CONVERT, которая имеет немного большие возможности. Общий вид команды:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Более мощные возможности проявляются при конвертировании дат, но об этом мы поговорим в следующем разделе. При преобразовании чисел и строк принцип работы схожий. В следующем примере целочисленное поле приводится к дробному с пятью символами после запятой:
SELECT CONVERT(decimal(10,5), idPeoples) FROM tbPeoples
С помощью команд CAST и CONVERT можно округлять числа. Например, в таблице товаров у нас есть поле для хранения цены, которое является дробным. Давайте округлим цену до рублей, отбросив все, что находится после запятой, т.е. копейки:
-- Округление с помощью CONVERT SELECT CONVERT(decimal(10,0), Цена) FROM Товары -- Округление с помощью CAST SELECT CAST(Цена AS decimal(10,0)) FROM Товары
Можно было бы привести и к целочисленному типу, но я решил использовать тип decimal, но явно указать, что после запятой должно быть 0 разрядов. В следующем примере после запятой остается только 1 разряд:
SELECT CONVERT(decimal(10,1), Цена) FROM Товары
2.22. Работа с датами и временем
Если вы посмотрите приложение 1, то увидите, что тип datetime может принимать значения от 1 Января 1753 и до 31 декабря 9999. Если посмотреть на дату 1 января 2005 года, то это будет число с плавающей точкой, означающее количество времени с точностью до 3.33 миллисекунды. Целая часть – это дата, а дробная часть – это время. В разделе 2.17 мы уже использовали особенность даты быть числом для увеличения даты на один день. В этой главе мы поговорим о датах более подробно.
2.22.1. Преобразование дат
Для начала посмотрим, как можно преобразовывать дату, раз уж в предыдущем разделе мы затронули эту тему.
В приложении 1, в описании типа datetime сказано, что SQL сервер использует формат даты, при котором вначале идет месяц (мм/дд/гггг). Если все оставить так, как есть, то пользователям будет неудобно вводить данные в таблицы, ведь мы привыкли начинать ввод даты с числа, а потом уже указывать месяц.
Для преобразования дат лучше всего использовать функцию CONVERT. Самый простейший пример преобразования с помощью этой функции:
SELECT convert(datetime, '31.1.2005' ,103)
Как мы уже знаем, после указания функции convert, в скобках указывается новый тип и переменная. Но у нас здесь три параметра. Что это за третий параметр. Это стиль, в котором представлена дата. Если посмотреть на дату ‘31.1.2005’, то видно, что вначале явно идет число, ведь 31-го месяца не существует. Если бы команда выглядела так: SELECT convert(datetime, ‘31.1.2005’), то сервер вернул бы нам ошибку, потому что не смог бы преобразовать дату.
Последний параметр как раз указывает номер стиля, в котором мы представили дату и благодаря ему сервер правильно прочитает параметр ‘31.1.2005’. Стили вы можете увидеть в таблице 2.1. Это наиболее часто используемые стили. Более полный вариант таблицы можно увидеть в файле помощи. В первой колонке показан номер стиля, если год показан в сокращенном виде (то есть без указания века, например 05). Во второй колонке номер стиля, если год указан полностью (например, 2005).
Таблица 2.1. Стили преобразования типа данных даты
| Сокращенный год | Полный год | Формат даты |
| — | 0 или 100 | Месяц дд гггг чч:ммAM (или PM) |
| 1 | 101 | мм/дд/гггг |
| 2 | 102 | гг.мм.дд |
| 3 | 103 | дд/мм/гггг |
| 4 | 104 | дд.мм.гггг |
| 5 | 105 | дд-мм-гггг |
| 6 | 106 | дд месяц гггг |
| 7 | 107 | месяц дд гггг |
| 8 | 108 | чч:мм:сс |
| 10 | 110 | мм-дд-гггг |
| 11 | 111 | гг/мм/дд |
| 12 | 112 | Ггммдд |
Исходя из этой таблицы, стиль 103 соответствует принятому в России формату дд/мм/гггг и именно его мы использовали.
В следующем запросе мы используем преобразование даты в операторе INSERT для вставки данных в таблицу:
INSERT INTO tbPeoples (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES('СОДОРОЧКИН', 'ИВАН', 'СЕРГЕЕВИЧ', 11, convert(datetime, '31.1.2005' ,103))
Есть еще один способ указания формата даты – с помощью оператора SET. Этот оператор мы будем достаточно подробно рассматривать в главе 4.1, но то, что касается преобразования даты мы рассмотрим сейчас. Посмотрите на следующий пример:
SET DATEFORMAT dmy GO DECLARE @vdate datetime SET @vdate = '29/11/00' SELECT @vdate
В первой строке указывается формат необходимой даты с помощью оператора SET DATEFORMAT. После этого указывается формат в виде букв d, m и y, которые отражают необходимую нам последовательность в указании даты. В данном случае мы говорим, что мы будем указывать в дате сначала число, потом месяц и в конце будет идти год.
Теперь объявляется переменная vdate, которой присваивается дата в заданном формате и эта дата выводиться на экран.
Необходимо отметить, что один раз выполнив команду SET, установленное значение действует в течение всей сессии. Но если отключиться и подключиться к серверу заново, сервер снова будет требовать ввода даты в формате по умолчанию.
2.22.2. Функции для работы с датами
Но на преобразовании весь свет не сошелся. Есть еще множество функций, встроенных в SQL сервер, с помощью которых можно работать с типом даты. В таблице 2.2 вы можете увидеть функции, которые есть у MS SQL Server для работы с датами и краткое описание. Далее мы рассмотрим примеры работы с этими функциями.
Таблица 2.2. Функции MS SQL Server по работе с датами
| Название функции | Описание |
| GETDATE | Определение текущей даты |
| DATEADD | Удобная функция увеличения значения даты |
| DATEDIFF | Определение разницы между двумя датами |
| DATENAME | Отображение имени части даты |
| DATEPART | Определение определенной части даты |
| DAY | Возвращает число, отражающее дни в дате |
| MONTH | Возвращает число, отражающее месяц в дате |
| YEAR | Возвращает число, отражающее год в дате |
Текущая дата
Наиболее часто используемая функция – GETDATE(), которая текущую дату. Если в таблице необходимо сохранять текущую дату, то лучше всего будет определять ее с помощью функции сервера, а не с помощью каких-либо сторонних средств (например, с помощью клиентской программы) на компьютере клиента. Почему? Во-первых, если в вашей сети компьютеры не настроены на синхронизацию времени, то часы на всех компьютерах могут идти по-разному. Во-вторых, за работу часов при выключенном питании в компьютере отвечает маленькая батарейка, которая имеет тенденцию разряжаться, и тогда часы начинают серьезно отставать.
Если время добавления записи берется от клиента, то нельзя гарантировать, что в таблице даты отражают реальную действительность, потому что появляется зависимость от правильности часов на всех компьютерах. Если дата устанавливается с помощью GETDATE(), то для всех клиентов при добавлении записи дата будет устанавливаться по часам сервера.
Я рекомендую для полей, которые должны отражать дату создания записи, еще при создании таблиц установить значение по умолчанию в виде функции GETDATE(). Например:
CREATE TABLE TestTable ( id int DEFAULT 1, dDate datetime DEFAULT (getdate()), vcName varchar(50) DEFAULT 'M' )
Теперь при добавлении записей нет необходимости указывать дату, потому что текущее значение будет использоваться по умолчанию.
Если вы не указали значение по умолчанию для поля типа datetime, то используйте функцию GETDATE() в запросе:
INSERT INTO Товары (Дата, [Название товара], Цена, Количество) VALUES(GETDATE(), 'Сок', 23, 1)
Для добавляемой строки будет установлена текущая дата по часам сервера, а не клиента, потому что именно сервер разбирает и выполняет запрос.
Увеличение даты
Для увеличения даты используется функция DATEADD. В общем виде она выглядит следующим образом:
DATEADD (datepart, number, date)
В скобках указывается три параметра:
- datepart – какую часть даты необходимо увеличить. Возможные значения этого параметра можно увидеть на 1;
- number – число, на которое надо увеличить;
- date – дата, которую надо изменить.
Таблица 2.3. Возможные значения параметра datepart
| Значение параметра Datepart | Описание |
| Year | Год |
| Quarter | Квартал |
| Month | Месяц |
| Dayofyear | День года |
| Day | День |
| Week | Неделя |
| Hour | Чвс |
| Minute | Минута |
| second | Секунда |
| Millisecond | Миллисекунда |
Допустим, что необходимо увеличить дату на 1 год. Для этого можно выполнить следующий запрос:
SELECT DATEADD(Year, 1, GETDATE())
Чтобы уменьшить значение даты, необходимо в качестве второго параметра указать отрицательное значение. Например, следующий пример уменьшает значение текущей даты на 1:
SELECT DATEADD(Year, -1, GETDATE())
Разница в дате
Нередко необходимо узнать разницу между двумя датами. Например, чтобы пользователь не указал слишком большой диапазон, можно проверять, чтобы разница между двумя датами не превышала год, иначе запрос будет возвращать слишком много данных. Пусть пользователь конкретизирует свои потребности.
Разницу можно определить с помощью функции DATEDIFF, которая имеет следующий вид:
DATEDIFF ( datepart , startdate , enddate )
Здесь также три параметра:
- Часть даты, разницу в которой необходимо определить. Возможные значения показаны в таблице 2.3;
- Начальная дата;
- Конечная дата.
Следующая команда определяет количество дней между двумя датами:
SELECT DATEDIFF(Day, '03.04.2005', '07.08.2005')
В результате на экране мы должны увидеть число 126 дней.
Имя даты
Некоторые пользователи любят смотреть на даты в виде символьных имен. Для этого используется функция DATENAME:
DATENAME ( datepart , date )
Посмотрим параметры этой функции:
- Часть даты, символьное имя в которой необходимо определить. Возможные значения показаны в таблице 2.3;
- Дата;
Несмотря на то, что в качестве первого параметра можно указывать любую часть даты, смысла от этого особо нет. Только при указании месяца будет возвращено символьное имя. Например, следующий запрос вернет нам в результате название 4-го месяца, т.е. April:
SELECT DATENAME(Month, '04.03.2005')
Если попытаться узнать символьное имя числа, то функция вернет нам это число.
Разбиение даты на части
Для определения определенной части даты можно одну из функций DAY, MONTH или YEAR (рассмотрим ниже в этой главе), но они ограничены в возможностях и заточены под определенную задачу. Более универсальной является функция DATEPART, которая имеет следующий внешний вид:
DATEPART ( datepart , date )
- Часть даты, значение которой необходимо определить. Возможные значения показаны в таблице 2.3;
- Дата;
Следующий пример определяет значение месяца в дате:
SELECT DATEPART(Month, '04.03.2005')
В результате мы получим число 4.
Но если нужно определить дату, месяц или год, то проще будет воспользоваться одной из следующих функций:
- DAY(дата) – возвращает значение числа, указанное в дате;
- MONTH(дата) – возвращает значение месяца;
- YEAR(дата) — возвращает значение года.
Например, с помощью следующего запроса определяется год, который указан в дате ‘04.03.2005’:
SELECT YEAR('04.03.2005')
2.22.3. Замечания по работе с датами
С типом datetime достаточно сложно работать. Рассмотрим следующие три запроса:
SELECT FROM Товары WHERE Дата
В этом запросе мы выбираем товары, купленные до 11 числа пятого месяца. Пока ничего удивительного. Но посмотрим на следующий запрос:
SELECT FROM Товары WHERE Дата
В этом запросе по идее запрашивается то же самое, ведь мы просим товары, купленные до 10-го числа включительно. Знак меньше заменен на «меньше либо равно», а дата уменьшена на 1 день. Вы удивитесь, но результат может быть другим. Почему? Для этого нужно вспомнить, что представляет собой тип datetime. Если в поле для даты сохраняется не только дата, но и время, то числа будут дробными, а мы сравниваем только с датой.
Возьмем число 10-е мая 2005-го года. Этой дате соответствует число 38481. Но если помимо даты в поле будет храниться и время, то число будет дробным, например, 38481,1943. Такое число удовлетворяет условию меньше ‘5.11.2005’ (38481,1943 < 38482), но не удовлетворяет условию меньше либо равно '5.10.2005' (38481,1943
Вы должны учитывать эту особенность при разработке собственных программ и четко понимать, какие данные хранятся в поле, и какие данные вы хотите получить из таблицы, иначе результат запроса может быть не точным.