Заметки по SQL 4: Преобразование в запросе строки в дату одним выражением

10.12.21

Разработка - Запросы

На "Инфостарте" есть несколько статей, описывающих преобразование строки в дату. Описанное в них преобразование осуществляется в несколько запросов и имеет сложные для понимания выражения. В статье описано относительно простое выражение в запросе, преобразующее строку в дату.

Для более простого понимания принципа преобразования, вначале, разобьем выражение на несколько запросов, а затем приведем преобразование к одному выражению.

//Собственно, первый запрос это ввод строки даты и разбиение ее на года, месяца и даты
ВЫБРАТЬ
	ПОДСТРОКА("20210816", 1, 4) КАК Год,
	ПОДСТРОКА("20210816", 5, 2) КАК Месяц,
	ПОДСТРОКА("20210816", 7, 2) КАК Число
ПОМЕСТИТЬ ВременнаяТаблица
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
// Поскольку работать мы будем в текущем тысячелетии, то стазу добавим в сумму годов 2000
// В результате диапазон преобразуемых лет, в данном примере будет 2000-2099,
// возможно к этому времени 2099 фирма 1с напишет собственную функцию
	2000 + 
//Преобразуем строчное значение числа с десятилетиями в число и умножаем на весовой коэффициент 10 
        ВЫБОР
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "1"
			ТОГДА 1
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "2"
			ТОГДА 2
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "3"
			ТОГДА 3
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "4"
			ТОГДА 4
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "5"
			ТОГДА 5
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "6"
			ТОГДА 6
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "7"
			ТОГДА 7
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "8"
			ТОГДА 8
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 3, 1) = "9"
			ТОГДА 9
		ИНАЧЕ 0
	КОНЕЦ * 10 + 
//Преобразуем строчное значение числа с единицами лет в число 
    ВЫБОР
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "1"
			ТОГДА 1
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "2"
			ТОГДА 2
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "3"
			ТОГДА 3
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "4"
			ТОГДА 4
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "5"
			ТОГДА 5
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "6"
			ТОГДА 6
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "7"
			ТОГДА 7
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "8"
			ТОГДА 8
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Год, 4, 1) = "9"
			ТОГДА 9
		ИНАЧЕ 0
//Отнимаем от конечного числа 1
	КОНЕЦ - 1 КАК Год,

//Преобразуем строчное значение десятков месяцев в число и умножаем на весовой коэффициент 10 
//Поскольку десятков месяцев не может быть больше 10, то и преобразование короткое
	ВЫБОР
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 1, 1) = "1"
			ТОГДА 1
		ИНАЧЕ 0
	КОНЕЦ * 10 + 
//Преобразуем строчное значение числа с единицами месяцев в число 
    ВЫБОР
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "1"
			ТОГДА 1
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "2"
			ТОГДА 
        КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "3"
			ТОГДА 3
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "4"
			ТОГДА 4
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "5"
			ТОГДА 5
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "6"
			ТОГДА 6
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "7"
			ТОГДА 7
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "8"
			ТОГДА 8
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Месяц, 2, 1) = "9"
			ТОГДА 9
		ИНАЧЕ 0
	КОНЕЦ - 1 КАК Месяц,

//Преобразуем строчное значение десятков дней в число и умножаем на весовой коэффициент 10 
//Поскольку десятков дней не может быть больше 30, то и преобразование проводится до 3-х единиц
	ВЫБОР
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 1, 1) = "1"
			ТОГДА 1
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 1, 1) = "2"
			ТОГДА 2
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 1, 1) = "3"
			ТОГДА 3
		ИНАЧЕ 0
	КОНЕЦ * 10 + 
//Преобразуем строчное значение дней в число, стандартно  
    ВЫБОР
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "1"
			ТОГДА 1
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "2"
			ТОГДА 2
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "3"
			ТОГДА 3
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "4"
			ТОГДА 4
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "5"
			ТОГДА 5
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "6"
			ТОГДА 6
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "7"
			ТОГДА 7
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "8"
			ТОГДА 8
		КОГДА ПОДСТРОКА(ВременнаяТаблица.Число, 2, 1) = "9"
			ТОГДА 9
		ИНАЧЕ 0
	КОНЕЦ - 1 КАК День
ПОМЕСТИТЬ ВременнаяТаблица1
ИЗ
	ВременнаяТаблица КАК ВременнаяТаблица
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
//Итоговое выражение преобразование выглядит следующим образом
//Поскольку итоговая дата формируется 
//как Дата 0001.01.01 + Число лет + число месяцев + число дней
//становится понятным для чено мы отнимали от итоговых сумм годов, месяцев, и дней 1 единицу

	ДОБАВИТЬКДАТЕ(
    ДОБАВИТЬКДАТЕ(
    ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1), ГОД,   ВременнаяТаблица1.Год), 
                                      МЕСЯЦ, ВременнаяТаблица1.Месяц), 
                                      ДЕНЬ,  ВременнаяТаблица1.День) КАК Дата
ИЗ
	ВременнаяТаблица1 КАК ВременнаяТаблица1

Приведем итоговый запрос с выражением преобразования строки в дату.

ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(ДОБАВИТЬКДАТЕ(ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1), 
        ГОД, 2000 + ВЫБОР
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "1"
						ТОГДА 1
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "2"
						ТОГДА 2
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "3"
						ТОГДА 3
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "4"
						ТОГДА 4
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "5"
						ТОГДА 5
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "6"
						ТОГДА 6
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "7"
						ТОГДА 7
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "8"
						ТОГДА 8
					КОГДА ПОДСТРОКА(&СтрокаДата, 3, 1) = "9"
						ТОГДА 9
					ИНАЧЕ 0
				КОНЕЦ * 10 + ВЫБОР
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "1"
						ТОГДА 1
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "2"
						ТОГДА 2
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "3"
						ТОГДА 3
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "4"
						ТОГДА 4
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "5"
						ТОГДА 5
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "6"
						ТОГДА 6
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "7"
						ТОГДА 7
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "8"
						ТОГДА 8
					КОГДА ПОДСТРОКА(&СтрокаДата, 4, 1) = "9"
						ТОГДА 9
					ИНАЧЕ 0
				КОНЕЦ - 1), 
         МЕСЯЦ, ВЫБОР
				КОГДА ПОДСТРОКА(&СтрокаДата, 5, 1) = "1"
					ТОГДА 1
				ИНАЧЕ 0
			КОНЕЦ * 10 + ВЫБОР
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "1"
					ТОГДА 1
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "2"
					ТОГДА 2
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "3"
					ТОГДА 3
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "4"
					ТОГДА 4
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "5"
					ТОГДА 5
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "6"
					ТОГДА 6
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "7"
					ТОГДА 7
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "8"
					ТОГДА 8
				КОГДА ПОДСТРОКА(&СтрокаДата, 6, 1) = "9"
					ТОГДА 9
				ИНАЧЕ 0
			КОНЕЦ - 1), 
        ДЕНЬ, ВЫБОР
			КОГДА ПОДСТРОКА(&СтрокаДата, 7, 1) = "1"
				ТОГДА 1
			КОГДА ПОДСТРОКА(&СтрокаДата, 7, 1) = "2"
				ТОГДА 2  
			КОГДА ПОДСТРОКА(&СтрокаДата, 7, 1) = "3"
				ТОГДА 3
			ИНАЧЕ 0
		КОНЕЦ * 10 + ВЫБОР
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "1"
				ТОГДА 1
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "2"
				ТОГДА 2
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "3"
				ТОГДА 3
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "4"
				ТОГДА 4
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "5"
				ТОГДА 5
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "6"
				ТОГДА 6
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "7"
				ТОГДА 7
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "8"
				ТОГДА 8
			КОГДА ПОДСТРОКА(&СтрокаДата, 8, 1) = "9"
				ТОГДА 9
			ИНАЧЕ 0
		КОНЕЦ - 1) КАК ДатаПреобразования

В примере в качестве параметра выступает строка "20210816". Зная принцип преобразования можно переделать запрос под строку "16.08.2021" или "16.08.21". По аналогичному принципу можно преобразовать часы, минуты, секунды. Вместо параметра может выступать любое поле данных имеющее тип "Строка".

В случае строки неограниченной длинны, возможно необходимо  будет произвести следующее преобразование-

ВЫРАЗИТЬ(Данные.СтрокаДата КАК СТРОКА(8)) КАК СтрокаДата
// Где 8 - это количество символов СтрокаДата 

Тестирование проводилось на платформе 1С:Предприятие 8.3 (8.3.16.1063).

запрос строка дата SQL

См. также

SALE! %

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

12000 10000 руб.

02.09.2020    93616    477    380    

531

Нахождение уникальных наборов строк таблицы запросом

Запросы Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Решение задачи нахождения уникальных наборов строк таблицы запросом

23.07.2023    4406    tormozit    78    

38

Структура запроса

Инструментарий разработчика Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Абонемент ($m)

Обработка использует схему запроса и строит визуальную структуру источников данных в виде дерева. Пригодится при разборе прежде всего чужих, громоздких запросов и позволит быстро получить список источников данных. Единственное ограничение - текст запроса должен соответствовать метаданным текущей конфигурации.

1 стартмани

21.06.2023    4296    50    obmailok    35    

53

MS SQL Server: изучаем планы запросов

Запросы HighLoad оптимизация Запросы Бесплатно (free)

Многие знают, что для ускорения работы запроса нужно «изучить план». При этом сам план обычно обескураживает: куча разноцветных иконок и стрелочек; ничего не понятно, но очень интересно! Аналитик производительности Александр Денисов на конференции Infostart Event 2021 Moscow Premiere рассказал, как выполняется план запроса и что нужно сделать, чтобы с его помощью находить проблемы производительности.

20.06.2023    7796    Филин    37    

92

Как собрать отладчиком отдельные части запроса в один

Запросы Платформа 1С v8.3 Запросы Россия Бесплатно (free)

Подробное описание функционала загрузки данных запроса из отладчика в консоли "Анализатор сложных запросов".

21.03.2023    3471    manuel    2    

19

Все консоли запросов для 1С

Запросы Бесплатно (free)

Список всех популярных обработок.

17.03.2023    19531    kuzyara    70    

148

Обработка результатов запроса произвольными вычисляемыми полями. Обзор некоторых новых функций СКД

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных Конфигурации 1cv8 Бесплатно (free)

В данной статье вспомним, как обрабатывать результаты запроса в вычисляемых полях СКД, а также сделаем небольшой обзор на новые функции СКД платформы 8.3.20.

07.02.2023    4783    quazare    7    

38

Идентификатор объекта в запросе. Вы этого хотели?

Запросы Механизмы платформы 1С Платформа 1С v8.3 Запросы Бесплатно (free)

В платформе 8.3.22 появилась возможность получать идентификатор в запросе. Лично я ждал этого давно, но по итогу ждал большего. Что не так?

12.01.2023    22679    dsdred    24    

83
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. quazare 3432 10.12.21 19:06 Сейчас в теме
можно поинтересоваться - к чему эти заморочки со строками?
2. IVC_goal 202 10.12.21 19:14 Сейчас в теме
(1)Конечно можно. Об этом будет следующая статья
3. CheBurator 3115 10.12.21 23:23 Сейчас в теме
В чем здесь смысл и красота? или какой-то лайфхак? по мне - так тут целый талмуд программирования, а не "одним выражением"
MasterZlo; Leon75; bulpi; +3 Ответить
6. IVC_goal 202 11.12.21 11:49 Сейчас в теме
(3)Замечу, в статье нет словосочетания красивое решение (красота). Это классический костыль, а костыли редко бывают красивыми. В статье пишется "относительно простое". Но все познается в сравнении вот существующие решения - Выразить строку как дату в запросе и очень на мой взгляд интересное Выразить строку как число и строку как дату в запросе. Можно сравнить и сделать выводы. А по поводу "или какой-то лайфхак?", есть хорошая шутка. Как бы толковый словарь Даля охарактеризовал слово лайфхак?
Слово "лайфхак" имеет иностранное происхождение состоящее из двух слогов и имеет не приличный смысл. Наиболее приличные переводы слова; любимый хрен, хороший хрен, большой хрен.
13. webester 26 12.12.21 06:14 Сейчас в теме
(6)
Замечу, в статье нет словосочетания красивое решение (красота)

У вас в заголовке написано: "Одним выражением". Заходишь а тут портянка. Именно об этом вам и сказали.
есть хорошая шутка. Как бы толковый словарь Даля охарактеризовал слово лайфхак?

Даль охарактеризовал бы его по существу, как оно и применяется: "ухищрение, жизненная хитрость". Как вы получили выражение "большой хрен" остается только догадываться, но возможно у вас свои "специфические" словари.
mrChOP93; MasterZlo; +2 Ответить
4. insurgut 207 11.12.21 08:01 Сейчас в теме
Простое решение в лоб :) Думал что-то экстраординарное будет.
CheBurator; +1 Ответить
5. sapervodichka 6563 11.12.21 11:26 Сейчас в теме
На практике сталкивался только с обратным: Дату в запросе преобразовать в строку чтобы соединить с полем номера, типа "№ " + Таб.Номер + " от " + Таб.Дата_В_Формате_День_Месяц_Год. Вот Дата_В_Формате_День_Месяц_Год выделяли в запросе таким образом (где параметр &ДатаПреобразования в тексте на свое поле запроса меняете):

ПОДСТРОКА("0123456789", ДЕНЬ(&ДатаПреобразования) / 10 + 1, 1) + ПОДСТРОКА("0123456789", СЕКУНДА(ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1), СЕКУНДА, 6 * ДЕНЬ(&ДатаПреобразования))) / 6 + 1, 1) 
+ "." + 
ПОДСТРОКА("0123456789", МЕСЯЦ(&ДатаПреобразования) / 10 + 1, 1) + ПОДСТРОКА("0123456789", СЕКУНДА(ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1), СЕКУНДА, 6 * МЕСЯЦ(&ДатаПреобразования))) / 6 + 1, 1) 
+ "." + 
ПОДСТРОКА("0123456789", ГОД(&ДатаПреобразования) / 1000 + 1, 1) + ПОДСТРОКА("0123456789", СЕКУНДА(ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1), СЕКУНДА, ГОД(&ДатаПреобразования) * 0.06)) / 6 + 1, 1) + ПОДСТРОКА("0123456789", СЕКУНДА(ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1), СЕКУНДА, ГОД(&ДатаПреобразования) * 0.6)) / 6 + 1, 1) + ПОДСТРОКА("0123456789", СЕКУНДА(ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1), СЕКУНДА, ГОД(&ДатаПреобразования) * 6)) / 6 + 1, 1) КАК Дата_В_Формате_День_Месяц_Год
Danila-Master; mrChOP93; asg.aleks; IVC_goal; +4 Ответить
7. IVC_goal 202 11.12.21 13:17 Сейчас в теме
(5)Очень изящное решение обратного преобразования +10
8. Leon75 11.12.21 17:47 Сейчас в теме
Наш фреймворк уменьшает возможности MS SQL/PostgreSQL. Мы мазохисты.
9. IVC_goal 202 11.12.21 21:26 Сейчас в теме
(8)С кем поведешся (1с) того и наберешся, или выживание любой ценой
10. Leon75 11.12.21 21:54 Сейчас в теме
(9) Слово "поведешься" содержит в себе и другие смыслы.
А тот кто повелся, тот...
11. ixijixi 1535 11.12.21 22:26 Сейчас в теме
В SQL же есть функции преобразования (CAST, CONVERT), почему бы 1С их не использовать, тем более интерпретатор 1С все равно все преобразует в запрос t-sql?
*ворчит по-стариковски
14. Leon75 12.12.21 16:56 Сейчас в теме
(11)"Тебе не нужно никуда попадать: ни в ад ни в рай.
Ты уже в Аду."
12. json 3245 11.12.21 23:33 Сейчас в теме
Заголовок "Заметки по SQL". Но в самой статье нет ничего про SQL, а только решение на языке запросов 1С.

Причем, насколько я понимаю, дата в строке ожидается в каком-то определенном формате. Каждая цифра даты должна быть в конкретной позиции строки. То есть решение далеко не универсальное, а на один конкретный случай.

С учетом того, что решение громоздкое и неуниверсальное, применить мало кто сможет.

p.s. Ну и еще непонятно зачем хранить дату в строке и преобразовывать строку в дату в запросе.
Даже если не уйти от того, чтобы хранить дату строкой, то можно было спокойно разбирать строку в дату в прикладном коде. Получилось бы более лаконичное и простое решение
15. IVC_goal 202 12.12.21 17:45 Сейчас в теме
(12) Друзья мои "прекрасен наш союз". В следующей статье я приведу конкретный пример использования данного преобразования
16. json 3245 13.12.21 05:59 Сейчас в теме
(15) эмм... А почему это ответом на мой пост?
Не вижу в этом ответе связи с моим постом.
Я так понимаю, лишь бы что-нибудь ответить....
17. kasper076 96 23.01.22 23:16 Сейчас в теме
Оставьте свое сообщение