Преобразовать дату sql в дату 1с
Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:
Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.
Формат даты по умолчанию обычно определяется настройкой БД. Продвинутые СУБД могут
автоматически определять некоторые форматы (DATE (‘2008.01.10’))
или как в Oracle имеют функцию преобразования (to_date(‘01.02.2003′,’dd.mm.yyyy’)).
Для упрощения во многих СУБД там, где подразумевается дата,
перед строкой необязательно ставить имя типа.
Интервал времени
Синтаксис и реализация интервалов отличается на разных СУБД.
Oracle
PostgreSQL
интервалы указываются в виде строки, в которой перечисляются значение и тип промежутка:
- microsecond — микросекунды;
- millisecond — милисекунды;
- second — секунды;
- minute — минуты;
- hour — часы;
- day — дни;
- week — недели;
- month — месяцы;
- year — года;
- century — век;
- millennium — тысячелетие.
Слова можно употреблять и во множественном числе.
Если интервал начинается с дней, то можно использовать короткий формат строки как
в Oracle для дневных интервалов.
MySQL
Только сложные интервалы, состоящие из более одного типа промежутков, указываются в строке.
Для этих целей введены дополнительные по сравнению с PostgreSQL имена для промежутков:
- second_microsecond — секунды и микросекунды, формат строки ‘s.m’;
- minute_microsecond — минуты и микросекунды, формат строки ‘m.m’;
- minute_second — минуты и секунды, формат строки ‘m:s’;
- hour_microsecond — часы и микросекунды, формат строки ‘h.m’;
- hour_second — часы, минуты и секунды, формат строки ‘h:m:s’;
- hour_minute — часы и минуты, формат строки ‘h:m’;
- day_microsecond — день и микросекунды, формат строки ‘d.m’;
- day_second — дни, часы, минуты и секунды, формат строки ‘d h:m:s’;
- day_minute — дни, часы и минуты, формат строки ‘d h:m’;
- day_hour — дни и часы, формат строки ‘d h’;
- year_month — года и месяцы, формат строки ‘y-m’.
MySQL интервалы используются в выражениях с временными типами данных,
использовать их в качестве конечного типа для столбцов запрещено.
Выражения и операции
cтроковые операции
|| — соединение строк, в некоторых СУБД операнды автоматически преобразуются в
строковый тип. В MS Access используется &
алгебраические операции
- + — сложение;
- — — вычитание;
- * — умножение;
- / — деление;
- mod — остаток от деления. Oracle: mod(6,2). MySql: 6 mod 2.
Операции + и — также используются при работе со временем и интервалами.
В Oracle и PostgreSQL возможна разница между датами.
Результат возвращается в виде интервала в днях. Ниже приведен пример добавления к дате
интервала.
Ко времени можно прибавлять целое число, но результат зависит от конкретной СУБД.
операции отношения
- — меньше;
- — меньше либо равно;
- > — больше;
- >= — больше либо равно;
- = — равно;
- <>,!= — не равно;
логические операции и предикаты
- and — логическое и;
- or — логическое или;
- nor — отрицание;
- between — определяет, находится ли значение в указанном диапазоне:
выражение IN (значение1. значениеn)
В качестве множества значений может служить корректная выборка
условные выражения
- case — условный оператор, имеющий следующий синтаксис:
- decode(expr,s1,r1[,sn,rn][,defr]) — сравнивает выражение expr с каждым выражением si
из списка. Если выражения равны то возвращается значение равное ri. Если ни одно
из выражений в списке не равно expr, то возвращается defr или NULL, если defr не было указано.
Эта функция доступна только в Oracle и в большинстве случае заменяет оператор CASE; - coalesce(arg1,…,argn) — возвращает первый аргумент в списке не равный null. Для двух
аргументов в Oracle можно воспользоваться функцией nvl; - greatest(arg1,…,argn) — возвращает наибольший аргумент в списке;
- least(arg1,…,argn) — возвращает наименьший аргумент в списке;
- nullif((arg1,arg2) — возвращает null если два аргумента равны, иначе первый
аргумент.
Ниже приведен пример использования выражения в запросе выбора данных.
прочие операции
В каждой СУБД свой набор операций, выше были приведены наиболее употребительные.
Например, в PosgreSQL можно использовать и такие операции:
- ^ — возведение в степень;
- |/ — квадратный корень;
- ||/ — кубический корень;
- ! — постфиксный факториал;
- !! — префиксный факториал;
- @ — абсолютное значение.
Обзор функций
математические функции
- abs(x) — абсолютное значение;
- ceil(x) — наименьшее целое, которое не меньше аргумента;
- exp(x) — экспонента;
- floor(x) — наибольшее целое, которое не больше аргумента;
- ln(x) — натуральный логарифм;
- power(x, y) — возводит x в степень y;
- round(x [,y]) — округление x до y разрядов справа от десятичной точки. По умолчанию
y равно 0; - sign(x) — возвращает -1 для отрицательных значений x и 1 для положительных;
- sqrt(x) — квадратный корень;
- trunc(x [,y]) — усекает x до у десятичных разрядов. Если у равно 0
(значение по умолчанию), то х усекается до целого числа. Если у меньше 0, от отбрасываются
цифры слева от десятичной точки.
Тригонометрические функции работают с радианами:
- acos(x) — арккосинус;
- asin(x) — арксинус;
- atan(x) — арктангенс;
- cos(x) — косинус;
- sin(x) — синус;
- tan(x) — тангенс.
строковые функции
- ascii(string) — возвращает код первого символа, эта функция обратна функции CHR;
- chr(x) — возвращает символ с номером х, в MySQL это функция char;
- length(string) — возвращает длину строки;
- lower(string) — понижает регистр букв;
- upper(string) — повышает регистр букв;
- ltrim(string1[, string2]) — удаляет слева из первой строки все символы
встречающиеся во второй строке. Если вторая строка отсутствует, то удаляются пробелы. В MySQL
второй аргумент не поддерживается; - rtrim(string1[, string2]) — аналогична функции ltrim, только удаление
происходит справа; - trim(string) — удаляет пробелы с обоих концов строки;
- lpad(string1, n[, string2]) — дополняет первую строку слева n символами из
второй строки, при необходимости вторая строка дублируется. Если string2 не указана, то
используется пробел; - rpad(string1, n[, string2]) — аналогична функции lpad, только присоединение
происходит справа; - replace(string1, c1, c2) — заменяет все вхождения символа/подстроки c1 на c2.
Для простого удаления всех вхождений c1, в качестве третьего аргумента надо указать пустую
строку (»). В Oracle третий аргумент не обязателен, и по умолчанию равен пустой строке; - instr(string1, string2[, a][, b]) — возвращает b вхождение строки string2
в строке string1 начиная с позиции a. Если a отрицательно, то поиск происходит справа. По
умолчанию a и b присваиваются значение 1. В MySQL последние два аргумента не поддерживаются. В
PostgreSQL данной функции нет, однако ее реализация дана в документации, как раз для
совместимости с Oracle; - substr(string, pos, len) — возвращает подстрку с позиции pos и длины len.
работа с датами
В рассматриваемых СУБД для обработки времени мало общего. Самый минимум у Oraсle:
- current_date — глобальная переменная содержащая текущую дату. Можно использовать и в других СУБД;
- trunc(d,s) — приводит дату к началу указанной временной отметки, например к началу месяца.
В PostgreSQL есть аналогичная функция date_trunc(s,d). В MySQL для этих целей может
использоваться функция date_format(d,s), но она возвращает результат в виде строки; - add_months(d,n) — добавляет к дате указанное число месяцев;
- last_day(d) — последний день месяца, содержащегося в аргументе;
- months_between(d1,d2) — возвращает число месяцев между датами.
Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:
- квартал — q, quarter;
- год — yyyy, year;
- месяц — mm, month;
- неделя — ww, week;
- день — dd, day;
- час — hh, hour;
- минута — mi, minute.
Такие функции как last_day в других СУБД реализуются с помощью арифметики времени и преобразования типов.
Так что при желании можно написать соответствующую функцию. Ниже приведена выборка последнего дня указанной даты.
Преобразование типов
Множество типов разрешенные для преобразования в констркуции CAST AS определяется
реализацией СУБД. Так в MySQL может преобразовать только следующие типы: binary[(n)],
char[(n)], date, datetime, decimal[(m[,d])], signed [integer], time, unsigned [integer].
А в Oracle, кроме преобразования встроенных типов, можно преобразовывать выборки со
множеством записей в массивы.
В PostgreSQL более расширенные возможности по преобразованию. Во-первых, можно добавить
собственное преобразование для встроенных и пользовательских типов. Во-вторых, есть
собственный более удобный оператор преобразования типов .
В большинстве случае необходимо преобразование в строку либо из строки. Для этого случаяСУБД предоставляют дополнительные функции.
функции Oracle
- to_char(date [,format[,nlsparams]]) — дату в строку;
- to_char(number [,format[,nlsparams]]) — число в строку;
- to_date(string[,format[,nlsparams]]) — строку в дату;
- to_number( string [ ,format[, nlsparams] ]) — строку в число;
- to_timestamp(string, format) — строку во время.
В этих функциях format описание формата даты или числа, а nlsparams — национальные
параметры. Формат строки для даты задается следующими элементами:
- «» — вставляет указанный в ковычках текст;
- AD, A.D. — вставляет AD с точками или без точек;
- ВС, B.C. — вставляет ВС с точками или без точек;
- СС, SCC — вставляет век, SCC возвращает даты ВС как отрицательные числа;
- D — вставляет день недели;
- DAY — вставляет имя дня, дополненное пробелами до длины в девять символов;
- DD — вставляет день месяца;
- DDD — вставляет день года;
- DY1 — вставляет сокращенное название дня;
- FF2 — вставляет доли секунд вне зависимости от системы счисления;
- НН, НН12 — вставляет час дня (от 1 до 12);
- НН24 — вставляет час дня (от 0 до 23);
- MI — вставляет минуты;
- MM — вставляет номер месяца;
- MOMn — вставляет сокращенное название месяца;
- MONTHn — вставляет название месяца, дополненное пробелами до девяти символов;
- RM — вставляет месяц римскими цифрами;
- RR — вставляет две последние цифры года;
- RRRR — вставляет весь год;
- SS — вставляет секунды;
- SSSSS — вставляет число секунд с полуночи;
- WW — вставляет номер недели года (неделя — 7 дней от первого числа, а не от понедельника до воскресенья);
- W — вставляет номер недели месяца;
- Y.YYY — вставляет год с запятой в указанной позиции;
- YEAR, SYEAR — вставляет год, SYEAR возвращает даты ВС как отрицательные числа;
- YYYY, SYYYY — вставляет год из четырех цифр, SYYYY возвращает даты ВС как отрицательные числа;
- YYY, YY, Y — вставляет соответствующее число последних цифр года.
Формат числовой строки задается следующими элементами:
- $ — вставляет знак доллара перед числом;
- В — вставляет пробелы для целой части десятичного числа, если она равна нулю;
- MI — вставляет знак минус в конце (например, ‘999.999mi’);
- S — вставляет знак числа в начале или в конце (например,’s9999′ или ‘9999s’);
- PR — записывает отрицательное число в уголвых скобках (например,’999.999pr’);
- D — вставляет разделитель десятичной точки в указанной позиции (например, ‘999D999’);
- G — вставляет групповой разделитель в указанной позиции (например,’9G999G999′). При этом дробная часть числа отбрасывается;
- С — вставляет ISO идентификатор валюты в начале или в конце числа (например, ‘с9999’ или ‘9999с’);
- L — вставляет локальный символ валюты в в начале или в конце числа (например, ‘l9999’ или ‘9999l’);
- , — вставляет запятую в указанной позиции вне зависимости от группового разделителя;
- . — вставляет десятичную точку в указанной позиции вне зависимости от разделителя десятичной точки;
- V — возвращает значение, умноженное на 10^n, где n равно числу девяток после V. В случае необходимости это значение округляется;
- ЕЕЕЕ — 9.99ЕЕЕЕ возвращает значение в экспоненциальной форме записи;
- RM — RM значение будет записано римскими цифрами в верхнем регистре;
- rm — rm значение будет записано римскими цифрами в нижнем регистре;
- 0 — вставляет нули, вместо пробелов в начале строки или в конце, например,
9990 вставляет нули, вместо пробелов в конце строки; - 9 — каждая 9 определяет значащую цифру.
функции PostgreSQL
- to_char(timestamp, format) — время в строку;
- to_char(interval, format) — интервал времени в строку;
- to_char(number, format) — число в строку;
- to_date(str, format) — строку в дату;
- to_number(str, format) — строку в число;
- to_timestamp(str, format) — строку во время.
Основные элементы форматирования совпадают с Oracle.
функции MySQL
При хранении даты в MySQL под типом Date (), она имеет формат 2011-07-11 (год-месяц-день). В некоторых случаях даже не имея разделителя 20110711.
Поскольку в русскоязычных странах более привычным к восприятию считается формат 11.07.2011 (день.месяц.год), то при выводе даты из базы данных, возникает необходимость в её преобразовании.
Преобразовать дату можно несколькими способами.
- при помощи php кода
- воспользовавшись командой DATE_FORMAT () при выборке из базы.
Первый способ применяется в тех случаях, когда необходимо вывести небольшое количество записей или же когда разработчик не подозревает о существовании второго способа.
Второй способ применим во всех случаях, вне зависимости сколько записей необходимо извлечь из базы, при этом он осуществляет минимальную нагрузку на сервер в отличии от способа с php кодом.
Рассмотрим пример выполнения:
Допустим существует таблица message , которая содержит ячейку send_data с датой в формате 2011-07-11 .
Для извлечения и преобразования даты напишем следующий код:
$message = mysql_fetch_array(mysql_query("SELECT DATE_FORMAT(send_data, '%e.%m.%Y') FROM message"));
Далее в том месте где необходимо вывести преобразованную дату, выводим массив $message любой, удобной для вас командой:
к примеру если в send_data находится 2011-05-03 то мы получим 03.05.2011 .
Номер индекса в массиве $message указываем каким по счету начиная от 0, в команде SELECT извлекается необходимое значение с преобразованной датой. К примеру при запросе:
$message = mysql_fetch_array(mysql_query("SELECT title, text, DATE_FORMAT(send_data, '%e.%m.%Y') FROM message"));
вывод даты будет осуществляться с индексом 2:
Преобразовать дату при помощи DATE_FORMAT() можно в любой вид и очередность при помощи подстановки ключей.
Ниже приведен список основных элементов форматирования для даты и времени:
Функция преобразование всяких вариантов строк в дату. Искал, но не нашел на сайте.
Преобразовывает СТРОКИ вида "01/10/13","01-10-2013","10,13","10.13","01.03.2013 0:00:00" к типу ДАТА
Может, кому пригодится.
В обработке используется стандартная функция разложить строку, указываю ее ниже.
Специальные предложения
Функция преобразование всяких вариантов строк в дату. Искал, но не нашел на сайте.
Преобразовывает СТРОКИ вида "01/10/13","01-10-2013","10,13","10.13","01.03.2013 0:00:00" к типу ДАТА
Может, кому пригодится.
В обработке используется стандартная функция разложить строку, указываю ее ниже.
(1) dv2008, Я уже думал, но у меня с разных мест идет дата в разных форматах, поэтому чтоб не гадать, поставил все на проверку.
(1),(2)по поводу параметра-разделителя
а он нужен?
попробуйте такое:
Код |
---|
Показать полностью |
попробуйте вместо минуса использовать другой символ, букву
попробуйте разные символы, напр., "012013"
будете приятно удивлены
(3) tdr1225,
попробовал Ваш код, у меня выдает ошибку. что с фигурными скобками, что с минусом:
: Преобразование значения к типу Дата не может быть выполнено
Спасибо. Очень пригодилась.
(6) Хочу сказать, что вся 1С далека от совершенства.
Согласна с (7) - Отдельное спасибо за открытость.
Как правило, необходимость перевода строки в дату возникает при загрузке информации откуда-нибудь (например, из текстового файла, или Экселя). В таком случае, написать парсер для даты совсем несложно - потому что все строки одного вида. Таким образом, универсальность здесь оказывается не то чтобы лишней, но может заметно замедлить загрузку. Тем не менее, безусловно, полезная вещь. Отдельное спасибо за открытость
вот пример с меньшей функциональностью и с меньшим временем выполнения:
Функция ДатаИзСтроки10(стрДата) экспорт // "01.12.2011" преобразует в '01.12.2011 0:00:00'
Попытка
возврат Дата(Сред(стрДата,7,4)+Сред(стрДата,4,2)+Лев(стрДата,2))
Исключение
возврат '00010101'
КонецПопытки;
КонецФункции // ДатаИзСтроки10()
Amara; Alalexey; imbaZeratul; enoty200shtyk; rabota.v8.1c; shunk; Tash.B; Idris1204; Happy_yahoo; NCHRobert; 1v7; elephant_x; ASV085; s0u1; Неопределено; nekit_rdx; DataReducer; simgo83; eugenek27; stroga; Irwin; PSKMOL; user721122; Hamma; dev_FISHER; paxanb; N2; rayastar; Jon2011; surr; codename-s; b00ker; wau8824ru; CatNotDog; Alex_IT; freeek; PhoenixAOD; Somebody1; Prog1CZUP31; SP2000; PokerFace; Brook; klinval; Doomino; angelochekss666; Gremlin; illUMI; Km91; Montecrizto; buganov; anig99; sanfoto; verad; TeploU; killovolt; + 55 – Ответить
(8) Можно вместо "Сред(стрДата,7,4)" Использовать "Прав(стрДата, 4)". И тогда время выполнения уменьшится еще сильнее.
Функция DATE_FORMAT преобразует дату из формата год-месяц-день или формата год-месяц-день часы:минуты:секунды в другой удобный нам формат.
К примеру, из год-месяц-день можно сделать день.месяц.год или месяц--год
См. также функцию TIME_FORMAT, которая меняет формат вывода времени.
Синтаксис
Команды
Можно использовать следующие команды, чтобы сделать нужный формат вывода:
Команда | Описание |
---|---|
%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'. |
%% | Символ `%'. |
Примеры
Все примеры будут по этой таблице workers, если не сказано иное:
id айди | name имя | date дата регистрации |
---|---|---|
1 | Дима | 2010-03-01 12:01:02 |
2 | Петя | 2011-04-02 13:02:03 |
3 | Вася | 2012-05-03 14:03:04 |
Пример
В данном примере при выборке создается новое поле, в котором будет лежать дата в другом формате:
В разделах этой статьи представлен обзор всех типов данных и функций даты и времени Transact-SQL.
Типы данных даты и времени
Типы данных даты и времени Transact-SQL перечислены в следующей таблице:
Тип данных | Формат | Диапазон | Точность | Объем памяти (в байтах) | Определяемая пользователем точность в долях секунды | Смещение часового пояса |
---|---|---|---|---|---|---|
time | чч:мм:сс[.ннннннн] | От 00:00:00.0000000 до 23:59:59.9999999 | 100 наносекунд | от 3 до 5 | Да | Нет |
date | ГГГГ-ММ-ДД | От 0001-01-01 до 31.12.99 | 1 день | 3 | Нет | Нет |
smalldatetime | ГГГГ-ММ-ДД чч:мм:сс | От 01.01.1900 до 06.06.2079 | 1 минута | 4 | нет | Нет |
datetime | ГГГГ-ММ-ДД чч:мм:сс[.ннн] | От 01.01.1753 до 31.12.9999 | 0,00333 секунды | 8 | Нет | Нет |
datetime2 | ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] | От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 | 100 наносекунд | От 6 до 8 | Да | Нет |
datetimeoffset | ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] [+|-]чч:мм | От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 (время в формате UTC) | 100 наносекунд | От 8 до 10 | Да | Да |
Тип данных Transact-SQL rowversion не относится к типам данных даты и времени. Тип данных timestamp является устаревшим синонимом rowversion.
Функции даты и времени
В следующих таблицах приводятся функции даты и времени Transact-SQL. Дополнительные сведения о детерминизме см. в статье Детерминированные и недетерминированные функции.
Функции, возвращающие значения системной даты и времени
Transact-SQL наследует все значения системной даты и времени от операционной системы компьютера, на котором работает экземпляр SQL Server.
Высокоточные функции системной даты и времени
SQL Server 2019 (15.x) получает значения даты и времени с помощью функции GetSystemTimeAsFileTime() Windows API. Точность зависит от физического оборудования и версии Windows, в которой запущен экземпляр SQL Server. Точность возвращаемых значений этого API-интерфейса задана равной 100 нс. Точность может быть определена с помощью метода GetSystemTimeAdjustment() API-интерфейса Windows.
Данная статья посвящена форматам дат в Oracle и некоторым особенностям их обработки. В статье приведен обзор нескольких стандартных масок форматирования дат, явная и неявная конвертация строк в даты и дополнительные параметры, влияющие на этот процесс. Как и в первой части статьи, обсуждение материала происходит на основе примеров, демонстрирующих нестандартные возможности форматирования. Детально рассмотрены механизмы Oracle, участвующие в процессе неявного преобразования. Описание большинства возможностей сопровождается ссылками на соответствующие разделы документации.
Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.
Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.
Функция to_date и форматы даты
Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number, to_char и to_date. Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы.
Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски 'DDD', которая возвращает номер дня в году:
Несмотря на явную пользу форматирования, я не планировал включать во вторую часть статьи обзор форматов дат и примеры использования экзотических масок. Во-первых, вряд ли это будет кому-то интересно, во-вторых, автор также не является большим почитателем сложного форматирования, поскольку редко его применяет в жизни. Единственная причина появления данного раздела – некоторые вопросы, возникшие у читателей по поводу использования формата RR.
Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.
Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?
Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида 'DD.MM.YY' или 'DD-MON-YYYY' мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. '2011'.
Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:
В запросе №4 время не указано, в запросе №5 указано только количество минут, часы и секунды опущены. В Oracle существует правило, согласно которому, если в дате отсутствует временной компонент, то время автоматически устанавливается в значение 00:00:00 (т.е. полночь), если задана только часть элементов времени (как в запросе №5), то пропущенные элементы устанавливаются в 00. Поэтому, запрос №4 вернет строку '03.02.2011 00:00:00', а запрос №5 — '03.02.2011 00:30:00'.
Вернемся к запросу №3, верно ли данное правило для дат, т.е. заменяются ли пропущенные при конвертации элементы даты на 00 или 01? Да заменяются, но не все, точнее, для пропущенных элементов даты используются значения из sysdate (первый день текущего месяца текущего года). Поэтому запрос №3 будет использовать 09 в качестве месяца и 2011 в качестве года, таким образом, результатом выполнения запроса будет дата 03.09.2011.
Пример №2. Порядок параметров форматирования
Выполнится ли следующий запрос, и если да, то какую дату он вернет?
На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.
Пример №3. Неявная конвертация
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: [D|DD] separator1 [MM|MON|MONTH] separator2 [R|RR|YY|YYYY]. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также " и удвоенную одинарную кавычку ''. Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:
Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.
Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:
Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:
То выполнение прошло бы успешно, и он вернул строку '09/15/2011 23:00:11'. Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка '15.09.11', далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:
Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка '15.09.11' не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.
Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG.
- Уровень БД: Параметры этого уровня устанавливаются при создании БД и прописываются в файле init.ora.
- Уровень экземпляра: Параметры этого уровня устанавливаются при старте экземпляра и могут быть изменены с помощью команды ALTER SYSTEM.
- Уровень сессии: Параметры этого уровня могут быть изменены командой ALTER SESSION. Также значение данных параметров можно проверить с помощью запроса:
Логично предположить, что преобразование строки '11.09.11' в дату пройдет успешно, а строки '11.SEP.11' – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в 'DD-MON-RR'). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.
Попробуем другой пример:
Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка '11.09.11' не удовлетворяет шаблону. Возможно, это мистика?
Увы, нет. Чтение документации показало, что существуют правила автозамены элементов форматирования даты. Ниже привожу таблицу замен.
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' | 'MON' and 'MONTH' |
'MON | 'MONTH' |
'MONTH' | 'MON' |
'YY' | 'YYYY' |
'RR' | 'RRRR' |
Пример №6. Формат RR vs YY
Большинству пользователей отличия масок RR и YY хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:
Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №1 для запроса №3. Таким образом, значение дня во всех полученных датах будет равно 01, а значение месяца 09 (если вы выполняете запрос в сентябре). Главный вопрос, каким будет значение года?
Как несложно предположить, в запросе №16 под '11' я подразумевал 2011 год и обе маски мне его вернули, т.е. результат выполнения запроса №16 это 01.09.2011 и 01.09.2011.
В запросе №17 под '99' я подразумевал 1999 год и тут мнения масок разделились: маска RR вернула ожидаемый 1999 год, а маска YY – 2099, т.е. результат выполнения запроса №17 это 01.09.1999 и 01.09.2099.
Рассмотрим, как работают эти элементы форматирования более детально:
- If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
- If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
- If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
- If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
Поэтому запрос №19 вернет 1950 год в обоих случаях.
Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:
Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.
Какой из запросов не выполнится?
Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.
В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?
Каждый раз, когда сессия с форматом даты по умолчанию 'DD.MON.RR' будет производить вставку с указанием только значения первого столбца будет возникать ошибка.
Особенности отображения дат в различных приложениях
Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.
Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.
Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:
Проверим, какие параметры для отображения дат использует sqlplus.
Рис. 1. Результат выполнения запроса №25 в sqlplus.
Как видно из рис.1, формат отображения даты меняется в зависимости от настроек сессии, т.е. sqlplus использует настройки сессии. Это упрощает понимание процесса преобразования дат в строки и обратно, поскольку и для преобразования и для отображения используются одинаковые маски.
Некоторые продвинутые средства разработки используют свои собственные NLS настройки, не имеющие отношения к настройкам Оракл. В качестве примера проверим, какие параметры для отображения дат использует PL/SQL Developer. Для этого выполним в нем запрос №25.
Рис. 2. Результат выполнения запроса №25 в PL/SQL Developer.
Как видно из рис.2, формат отображения даты не меняется при изменении настроек сессии. Более того, если посмотреть внимательно, то видно, что и первый и второй результаты вывода даты на экран не соответствовали параметрам сессии (в первом случае выведенная дата имела год в четырехзначном формате, а маска указывала год в двухзначном формате). Это означает, что утилита использует собственные NLS настройки, в случае PL/SQL Developer’а их расположение указано на рис. 3.
Рис. 3. Установка NLS параметров отображения дат в PL/SQL Developer.
Чем могут быть вредны NLS настройки утилит
Отображение даты в формате отличном от формата сессии вредно по одной причине – оно вводит пользователя в заблуждение и может привести к возникновению ошибок. Выполним в sqlplus и PL/SQL Developer следующий запрос:
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.
Результаты выполнения запроса представлены на рисунках ниже.
Рис. 4. Результат выполнения запроса №26 в sqlplus.
Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.
Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.
Заключение
В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.
Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.
Читайте также: