gifts2017

Выразить строку как дату в запросе

Опубликовал Jack Smith (InformSES) в раздел Программирование - Практика программирования

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

Иногда возникают ситуации, когда очень не хочется производить программную обработку данных после получения их запросом. Например, в нашем случае, когда более приемлимо непосредственно в запросе разобрать строки и вернуть уже их нормальное значение. Этим мы и займемся...

 

1.) имеем начальную таблицу строковых представлений дат в формате ГГГГММДД, заполним ее для примера:

//////////////////////////////////////////////////////////////////////////////// 
ВЫБРАТЬ
    "20110401"КАК Дата
ПОМЕСТИТЬ ВТ_Даты
 
ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "20090807"

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "20090909"
;
//////////////////////////////////////////////////////////////////////////////// 
 

Получаем таблицу:

 
Дата
"20110401"
"20090807"
"20090909"
 

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

2.) теперь переходим к разработке самого механизма преобразования, для начала нам понадобится таблица, содержащая соответствия строковых представлений цифр и их числовых значений. Эта таблица нам необходима для дальнейшего сопоставления символов из строкового представления дат числам:

 
////////////////////////////////////////////////////////////////////////////////  
ВЫБРАТЬ
    0 КАК Цифра,
    "0" КАК ЦифраСтрокой
ПОМЕСТИТЬ ВТ_Цифры

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    1,
    "1"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    2,
    "2"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    3,
    "3"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    4,
    "4"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    5,
    "5"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    6,
    "6"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    7,
    "7"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    8,
    "8"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    9,
    "9"
;
//////////////////////////////////////////////////////////////////////////////// 
 

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


//////////////////////////////////////////////////////////////////////////////// 
ВЫБРАТЬ
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 1, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК ПерваяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 2, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК ВтораяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 3, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК ТретьяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 4, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК ЧетвертаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 5, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК ПятаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 6, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК ШестаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 7, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК СедьмаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 8, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0
        КОНЕЦ) КАК ВосьмаяЦифра,
    ВТ_Даты.Дата
ПОМЕСТИТЬ ВТ_ЦифрыИзДат
ИЗ     ВТ_Даты КАК ВТ_Даты
        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Цифры КАК ВТ_Цифры
        ПО (ПОДСТРОКА(ВТ_Даты.Дата, 1, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 2, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 3, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 4, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 5, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 6, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 7, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 8, 1) = ВТ_Цифры.ЦифраСтрокой)
ГДЕ
   (НЕ ВТ_Даты.Дата ЕСТЬ NULL ) 
СГРУППИРОВАТЬ ПО
    ВТ_Даты.Дата
;
//////////////////////////////////////////////////////////////////////////////// 

Получаем таблицу, часть которой будет представлена в виде:


Первая цифра Вторая цифра Третья цифра Четвертая цифра Пятая цифра Шестая цифра Седьмая цифра Восьмая цифра
2 0 1 1 0 4 0 1
2 0 0 9 0 8 0 7
2 0 0 9 0 9 0 9

4.) далее, получим таблицу, содержащую числовые значения частей дат (год, месяц, день):


////////////////////////////////////////////////////////////////////////////////  
ВЫБРАТЬ
    ВТ_ЦифрыИзДат.ПерваяЦифра * 1000 + ВТ_ЦифрыИзДат.ВтораяЦифра * 100 + ВТ_ЦифрыИзДат.ТретьяЦифра * 10 + ВТ_ЦифрыИзДат.ЧетвертаяЦифра КАК ГодЧислом,
    ВТ_ЦифрыИзДат.ПятаяЦифра * 10 + ВТ_ЦифрыИзДат.ШестаяЦифра КАК МесяцЧислом,
    ВТ_ЦифрыИзДат.СедьмаяЦифра * 10 + ВТ_ЦифрыИзДат.ВосьмаяЦифра КАК ДеньЧислом,
    ВТ_ЦифрыИзДат.Дата
ПОМЕСТИТЬ ВТ_ГодМесяцДеньЧислом
ИЗ     ВТ_ЦифрыИзДат КАК ВТ_ЦифрыИзДат
;
//////////////////////////////////////////////////////////////////////////////// 
 

часть таблицы будет иметь следующий вид:

 
Год Месяц День
2011 4 1
2009 8 7
2009 9 9

5.) теперь остается преобразовать данные из полученной таблицы в нормальные даты, но и тут не все так просто как кажется, т.к. в функцию ДАТАВРЕМЯ языка запросов мы не можем передать полученные данные по причине появления ошибок в запросе, связанных с неумением языка запросов понимать и преобразовывать типы данных в переданные функции, поэтому идем более трудным путем:

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

Итак, из полученной таблицы вида:  

Дата
"20110401"
"20090807"
"20090909"
* где Дата имела тип Строка
 

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

 
Дата
2011.04.01
2009.08.07
2009.09.09
* где Дата уже имеет тип Дата
 

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

 

Полный текст нашего запроса

 
ВЫБРАТЬ
    "20110401" КАК Дата
ПОМЕСТИТЬ ВТ_Даты

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "20090807"

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "20090909"
;

////////////////////////////////////////////////////////////////////////////////  
ВЫБРАТЬ
    0 КАК Цифра,
    "0" КАК ЦифраСтрокой
ПОМЕСТИТЬ ВТ_Цифры

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    1,
    "1"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    2,
    "2"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    3,
    "3"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    4,
    "4"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    5,
    "5"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    6,
    "6"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    7,
    "7"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    8,
    "8"

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    9,
    "9"
;

////////////////////////////////////////////////////////////////////////////////  
ВЫБРАТЬ
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 1, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК ПерваяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 2, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК ВтораяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 3, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК ТретьяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 4, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК ЧетвертаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 5, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК ПятаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 6, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК ШестаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 7, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК СедьмаяЦифра,
    МАКСИМУМ(ВЫБОР
            КОГДА ПОДСТРОКА(ВТ_Даты.Дата, 8, 1) = ВТ_Цифры.ЦифраСтрокой
                ТОГДА ВТ_Цифры.Цифра
            ИНАЧЕ 0         КОНЕЦ) КАК ВосьмаяЦифра,
    ВТ_Даты.Дата
ПОМЕСТИТЬ ВТ_ЦифрыИзДат
ИЗ     ВТ_Даты КАК ВТ_Даты
        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Цифры КАК ВТ_Цифры
        ПО (ПОДСТРОКА(ВТ_Даты.Дата, 1, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 2, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 3, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 4, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 5, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 6, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 7, 1) = ВТ_Цифры.ЦифраСтрокой
                ИЛИ ПОДСТРОКА(ВТ_Даты.Дата, 8, 1) = ВТ_Цифры.ЦифраСтрокой)
ГДЕ
   (НЕ ВТ_Даты.Дата ЕСТЬ NULL ) 
СГРУППИРОВАТЬ ПО     ВТ_Даты.Дата
;

////////////////////////////////////////////////////////////////////////////////  
ВЫБРАТЬ
    ВТ_ЦифрыИзДат.ПерваяЦифра * 1000 + ВТ_ЦифрыИзДат.ВтораяЦифра * 100 + ВТ_ЦифрыИзДат.ТретьяЦифра * 10 + ВТ_ЦифрыИзДат.ЧетвертаяЦифра КАК ГодЧислом,
    ВТ_ЦифрыИзДат.ПятаяЦифра * 10 + ВТ_ЦифрыИзДат.ШестаяЦифра КАК МесяцЧислом,
    ВТ_ЦифрыИзДат.СедьмаяЦифра * 10 + ВТ_ЦифрыИзДат.ВосьмаяЦифра КАК ДеньЧислом,
    ВТ_ЦифрыИзДат.Дата
ПОМЕСТИТЬ ВТ_ГодМесяцДеньЧислом
ИЗ     ВТ_ЦифрыИзДат КАК ВТ_ЦифрыИзДат
;

////////////////////////////////////////////////////////////////////////////////  
ВЫБРАТЬ
    ДОБАВИТЬКДАТЕ(ДОБАВИТЬКДАТЕ(ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0), ГОД, ВТ_ГодМесяцДеньЧислом.ГодЧислом - 1), МЕСЯЦ, ВТ_ГодМесяцДеньЧислом.МесяцЧислом - 1), ДЕНЬ, ВТ_ГодМесяцДеньЧислом.ДеньЧислом - 1) КАК Поле1,
    ВТ_ГодМесяцДеньЧислом.Дата
ИЗ     ВТ_ГодМесяцДеньЧислом КАК ВТ_ГодМесяцДеньЧислом 
© senior-soft.ru Все права защищены.

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Дмитрий Глеков (glek) 18.01.13 16:49
Завёрнуто, но прикольно
2. Андрей Комар (akomar) 18.01.13 17:44
3. Доржи Балбаров (Angeros) 21.01.13 08:49
Это Может быть нужно в случае если дата в таком виде в базе храниться, а так если она из файла взята, ее можно предварительно преобразовать...
4. Владимир Каракозов (karakozov) 21.01.13 10:21
Хорошая шпаргалка по запросам, возможно и такое решение.
5. Ганеев Руслан (GANR) 21.01.13 11:41
Классная методика, я от неё балдею!
6. БИЗНЕС РЕШЕНИЯ (Para_1987) 21.01.13 15:39
Офигеть) не тестил, но прикольно придумано...молор! + за запросик)
7. Алексей Горнеев (gorneev1) 21.01.13 15:43
Классно придумано!!! Хорош запросик
8. TMV 21.01.13 17:34
9. Navern (Navern) 22.01.13 02:47
10. Роман Осадченко (cleaner_it) 22.01.13 11:43
11. Виталий (nafa) 22.01.13 13:19
Прикольно, конечно.
Но, помоему, учитывая то, что в реальности даты нужны не от Рождества Христова а максимум за 10 лет, проще:
1. сгенерировать программно таблицу значений типа ДатаСтрока- ДатаДата и добавить ее в запрос как временную таблицу
2. В УТ есть регистр сведений "регламентированный производственный календарь" - добавьте туда реквизит "Дата строкой" - дальше то же самое.
3. В СКД можно использовать функцию общего модуля, которая выполнит соответствующее преобразование.
12. Jack Smith (InformSES) 22.01.13 13:25
(11) nafa,
1. Если речь идет о датах рождения, то таблица может быть слишком велика
2. Регистр так же может быть не заполнен, например, за 1960 год
3. Да, в СКД конечно можно использовать функции общих модулей
4. Можно так же сначала провести программно обработку данных и затем впихнуть в любое нужное место, но публикация не об этом :)
13. Hany (Hany) 22.01.13 13:32
(12) InformSES,

но публикация не об этом :)


публикация-то может и не о том, но в комментариях можно писать все, что с ней связано.
Интересно, что обратное преобразование в энное раз короче по длине кода:)
Добавлю тут ссылку на мисту, где автор разместил в посте #23 преобразование даты в строку в запросе. Очень удобно, часто пользуюсь.
http://www.forum.mista.ru/topic.php?id=388253
14. ффф ыыы (zqzq) 22.01.13 13:34
Жесть какая. При том что в T-SQL это одной строкой делается.
15. Владимир Каракозов (karakozov) 24.01.13 08:58
Полезная методичка, такая ситуация бывает довольно часто. Выход из нее конечно находится, но данная публикация упрощает задачу. + автору
16. Алексей 1 (AlX0id) 24.01.13 10:21
(15) karakozov,
упрощает задачу

Посмотрю я на Вас, как будете анализировать подобные запросы за автором..
zqzq; FractalizeR; bulpi; +3 Ответить
17. Сергей (ildarovich) 24.01.13 11:48
В статье Выразить строку как число и строку как дату в запросе приведены более простые и гибкие запросы для решения той же задачи. Можно обойтись всего двумя запросами в пакете.
FractalizeR; bulpi; +2 Ответить
18. Денис (Den_D) 28.01.13 10:42
(17) Хотя вариант автора более нагляден и понятен, чем приведенный вами)
19. serge_focus (serge_focus) 24.08.13 20:29
Поставил ПЛЮС за обобщение и возможность использовать материал, как методичку по изучению возможностей работы с Запросами.
Не смотря на критические замечания - ценность материалов прежде всего в этом.
20. Марина Чирина (chmv) 01.07.14 09:51
21. Марина Чирина (chmv) 04.08.14 15:18
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа