Заметки по 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! 20%

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

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

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

10000 8000 руб.

02.09.2020    122421    673    389    

716

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5757    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2007    2    Yashazz    0    

29

Запрос 1С copilot

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

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6301    31    mkalimulin    25    

50

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

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

14.12.2023    1750    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

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

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5399    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16203    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. quazare 3587 10.12.21 19:06 Сейчас в теме
можно поинтересоваться - к чему эти заморочки со строками?
2. IVC_goal 213 10.12.21 19:14 Сейчас в теме
(1)Конечно можно. Об этом будет следующая статья
3. CheBurator 3119 10.12.21 23:23 Сейчас в теме
В чем здесь смысл и красота? или какой-то лайфхак? по мне - так тут целый талмуд программирования, а не "одним выражением"
MasterZlo; Leon75; bulpi; +3 Ответить
6. IVC_goal 213 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 6754 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 213 11.12.21 13:17 Сейчас в теме
(5)Очень изящное решение обратного преобразования +10
8. Leon75 11.12.21 17:47 Сейчас в теме
Наш фреймворк уменьшает возможности MS SQL/PostgreSQL. Мы мазохисты.
9. IVC_goal 213 11.12.21 21:26 Сейчас в теме
(8)С кем поведешся (1с) того и наберешся, или выживание любой ценой
10. Leon75 11.12.21 21:54 Сейчас в теме
(9) Слово "поведешься" содержит в себе и другие смыслы.
А тот кто повелся, тот...
11. ixijixi 1777 11.12.21 22:26 Сейчас в теме
В SQL же есть функции преобразования (CAST, CONVERT), почему бы 1С их не использовать, тем более интерпретатор 1С все равно все преобразует в запрос t-sql?
*ворчит по-стариковски
14. Leon75 12.12.21 16:56 Сейчас в теме
(11)"Тебе не нужно никуда попадать: ни в ад ни в рай.
Ты уже в Аду."
12. json 3297 11.12.21 23:33 Сейчас в теме
Заголовок "Заметки по SQL". Но в самой статье нет ничего про SQL, а только решение на языке запросов 1С.

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

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

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