在Excel文件数据库中基于日期的SQL查询 [英] SQL query based on date on an Excel file database

问题描述

我使用ADO以便在Excel文件数据库上执行SQL查询，并且在使用以下查询时遇到问题:

I use ADO in order to execute SQL queries on an Excel file database and I have a problem while using the following query :

strQuery = "SELECT NUM,CREATION_DATE,CREATOR,STATE,LAST_MODIFICATION_DATE,LAST_MODIFIER,CATEGORY,MODEL_LABEL FROM [MySheet$] WHERE CREATION_DATE<='" + EndDateTextBox.Value + "' AND CREATION_DATE>='" + BeginDateTextBox.Value + "'"

该查询已执行，但是当我在BeginDateTextBox中输入2010-01-01和EndDateTextBox中输入2016-01-01时，我没有任何结果，这是不正常的，因为数据库中的日期在2014年和2014年之间有所不同2015.

The query is executed but I don't get any result when I enter 2010-01-01 in the BeginDateTextBox and 2016-01-01 in the EndDateTextBox, which is not normal because the dates in my database vary between 2014 and 2015.

这是我的数据库的CREATION_DATE列的屏幕截图:

Here is a screenshot of the CREATION_DATE column of my database :

PS:我知道将Excel文件用作数据库不是最佳实践，但我被告知要这样做.

PS : I know that using Excel files as a database is not the best practice but I'm told to do so.

推荐答案

您需要将日期转换为值，ADO无法识别工作表中单元格的格式，只有值适用.所以最终查询应该是这样的:

You need to convert dates to values, ADO can't recognize format of the cells in sheet, only values applicable. So final query should be something like this:

SELECT * FROM [MySheet$] WHERE CREATION_DATE <= 42370 AND CREATION_DATE >= 40179

您可以使用以下方法实现此目的:

you can achieve this using such method:

Sub test()
Dim DtStart as Long
DtStart = Clng(Cdate("2010-01-01"))
Msgbox DtStart
End Sub

因此，最终的SQL查询将是:

so, the final SQL query will be:

strQuery = "SELECT NUM,CREATION_DATE,CREATOR,STATE,LAST_MODIFICATION_DATE,LAST_MODIFIER,CATEGORY,MODEL_LABEL " & _
           "FROM [MySheet$] " & _
           "WHERE CREATION_DATE <= " & CLng(CDate(EndDateTextBox.Value)) & _
           "  AND CREATION_DATE >= " & CLng(CDate(BeginDateTextBox.Value)) 

或者它可以是这样的:

strQuery = "SELECT NUM,CREATION_DATE,CREATOR,STATE,LAST_MODIFICATION_DATE,LAST_MODIFIER,CATEGORY,MODEL_LABEL " & _
           "FROM [MySheet$] " & _
           "WHERE CREATION_DATE BETWEEN " & _ 
            CLng(CDate(BeginDateTextBox.Value)) & " AND " & CLng(CDate(EndDateTextBox.Value)) 

此处是测试结果:

这篇关于在Excel文件数据库中基于日期的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！