Как в SQL-запросе к Excel через ADO получить начало месяца или отфильтровать данные по дате

← На главную

При работе с файлами Excel как с источниками данных через механизм ADO (ActiveX Data Objects) программисты часто сталкиваются с тем, что привычный синтаксис T-SQL (используемый в MS SQL Server) не работает. Это происходит потому, что при подключении к Excel через драйверы Microsoft используется диалект Jet SQL или ACE SQL, который ближе к синтаксису Microsoft Access, чем к серверным СУБД. Разберем подробно, как правильно работать с полями типа «Дата» (Type=135) и эффективно вычислять начало месяца или фильтровать данные порциями.

Причина неработоспособности стандартных запросов

Рассмотрим типичную ошибку. Попытка использовать функции dateadd, datepart и convert в одном запросе к Excel приводит к сбою, так как драйвер просто «не понимает» этих конструкций. В Jet/ACE SQL функции работы с датами имеют другие имена и сигнатуры. Например, вместо datepart(day, ...) следует использовать функцию Day(), а синтаксис DateAdd требует передачи интервала в виде строкового литерала (например, 'd' для дней).

Решение 1: Использование функции DateSerial

Проанализируем наиболее изящный способ получения начала месяца. Функция DateSerial(год, месяц, день) позволяет сконструировать дату из отдельных составляющих. Это самый надежный метод, так как он возвращает типизированный результат «Дата».

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


ТекстЗапроса = "SELECT 
               |    ДатаИзменения, 
               |    DateSerial(Year(ДатаИзменения), Month(ДатаИзменения), 1) AS НачалоМесяца 
               |FROM [" + ИмяЛиста + "$]";

Преимущество этого метода: он исключает ошибки округления и проблемы с форматами строк, так как работает напрямую с числовым представлением даты в Excel.

Решение 2: Математический расчет через DateAdd и Day

Если по каким-то причинам использование DateSerial неудобно, мы можем применить математический подход: вычесть из текущей даты количество прошедших дней месяца минус один. Выясним, как это выглядит на практике в диалекте Jet SQL.

Синтаксис функции DateAdd здесь следующий: DateAdd(интервал, число, дата). Обратите внимание, что количество дней, которое нужно прибавить (или вычесть), вычисляется на основе функции Day().


// Вычитаем (ДеньМесяца - 1) из текущей даты
ТекстЗапроса = "SELECT 
               |    DateAdd('d', 1 - Day(ДатаИзменения), ДатаИзменения) AS НачалоМесяца 
               |FROM [" + ИмяЛиста + "$]";

В этом примере 'd' указывает драйверу, что мы работаем с днями. Если ДатаИзменения равна 15.10.2023, то Day(ДатаИзменения) вернет 15. Выражение 1 - 15 даст -14. Прибавив -14 дней к 15 числу, мы получим ровно 1-е число месяца.

Решение 3: Фильтрация данных по месяцам (условие WHERE)

Зачастую вычисление начала месяца нужно не для отображения, а для фильтрации данных (например, чтобы обрабатывать файл порциями). Рассмотрим ситуацию, когда нам нужно выбрать данные за конкретный октябрь 2023 года. Мы можем пойти двумя путями: использовать функции сравнения или строковое форматирование.

Вариант А: Использование функций Year и Month


ТекстЗапроса = "SELECT * 
               |FROM [" + ИмяЛиста + "$] 
               |WHERE Month(ДатаИзменения) = 10 AND Year(ДатаИзменения) = 2023";

Вариант Б: Использование функции Format

Функция Format() позволяет преобразовать дату в строку заданного вида. Это очень удобно для группировки данных или условий:


ТекстЗапроса = "SELECT * 
               |FROM [" + ИмяЛиста + "$] 
               |WHERE Format(ДатаИзменения, 'yyyy-mm') = '2023-10'";

Важные технические нюансы работы через ADO

При реализации запросов к Excel важно учитывать настройки самого подключения. Рассмотрим ключевые параметры:

Выбор провайдера: Если вы работаете в 64-битной версии 1С, вам необходимо использовать провайдер Microsoft.ACE.OLEDB.12.0 (или выше). Старый Microsoft.Jet.OLEDB.4.0 работает только в 32-битных приложениях.
Типизация данных (IMEX): В строке подключения рекомендуется использовать параметр IMEX=1. Это заставляет драйвер более гибко подходить к определению типов данных, если в колонке встречаются и даты, и пустые значения.
Символ решетки (#) для литералов: Если вы передаете дату в запрос как константу, она должна быть обрамлена символами #. Например: WHERE ДатаИзменения >= #01/01/2023#.

Проанализируем пример правильной строки подключения для современного файла XLSX:


СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ИмяФайла + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";

Обработка результата в 1С

Если сложность SQL-запроса кажется избыточной, всегда остается вариант получить сырые данные в Recordset и выполнить преобразование уже средствами встроенного языка 1С. Однако помните, что фильтрация на стороне провайдера (через WHERE) всегда работает быстрее, чем перебор всех строк в коде 1С, особенно на больших файлах.

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