VB.NET操作SQLite数据库实现日期范围查询问题求助

核心问题拆解 #

1. SQLite函数错误：你用了Format([DATE],'dd.MM.yyyy')，但SQLite原生没有Format函数，这会导致查询语法错误。
2. 日期存储与比较逻辑错误：DATE字段定义为Integer类型却存储dd.MM.yyyy格式的字符串，再将DateTimePicker的日期转成字符串后强转Double，这种转换会让日期顺序完全错乱——比如31.12.2023转成Double是31.122023，01.01.2024是1.012024，数值上前者更大，但实际日期更早，直接导致范围查询结果错误。
3. 非参数化查询风险：直接拼接字符串生成SQL语句，不仅容易出错，还存在SQL注入风险。

修正方案 #

方案1：适配现有字段存储（临时解决） #

如果暂时不想修改DATE字段的存储方式，需要将数据库中dd.MM.yyyy格式的字符串转成SQLite可识别的yyyy-MM-dd格式，再和DateTimePicker的日期进行比较：

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ' 把DateTimePicker的日期转成SQLite标准日期格式
    Dim startDate As String = DateTimePicker1.Value.ToString("yyyy-MM-dd")
    Dim endDate As String = DateTimePicker2.Value.ToString("yyyy-MM-dd")

    ' 将数据库中的dd.MM.yyyy字符串转成yyyy-MM-dd后做范围查询
    QUERY = "SELECT * FROM [DATA] WHERE date(substr([DATE],7,4) || '-' || substr([DATE],4,2) || '-' || substr([DATE],1,2)) BETWEEN @StartDate AND @EndDate"
    
    CON = New SQLiteConnection("Data Source=PERSON.sqlite;Version=3;")
    DA = New SQLiteDataAdapter(QUERY, CON)
    ' 使用参数化查询，避免错误和注入风险
    DA.SelectCommand.Parameters.AddWithValue("@StartDate", startDate)
    DA.SelectCommand.Parameters.AddWithValue("@EndDate", endDate)
    
    DT = New DataTable
    DA.Fill(DT)
    DataGridView1.DataSource = DT.DefaultView
    
    ' 自动释放连接资源
    CON.Dispose()
End Sub

同时修正Form1_Load中的Format函数错误：

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' 用字符串拼接替代不存在的Format函数
    QUERY = "Select [ID],[NAME], substr([DATE],1,2) || '.' || substr([DATE],4,2) || '.' || substr([DATE],7,4) as DATES From [DATA]"
    
    CON = New SQLiteConnection("Data Source=PERSON.sqlite;Version=3;")
    DA = New SQLiteDataAdapter(QUERY, CON)
    DT = New DataTable
    DA.Fill(DT)
    DataGridView1.DataSource = DT.DefaultView
    
    CON.Dispose()
End Sub

方案2：优化日期存储（推荐） #

将DATE字段改成存储Unix时间戳（Integer类型），这是SQLite推荐的日期存储方式之一，查询效率更高：

插入数据时的处理（示例）

' 将DateTime转成Unix时间戳（秒级）
Dim timestamp As Integer = CInt(DateTimePicker.Value.ToUniversalTime().Subtract(New DateTime(1970, 1, 1)).TotalSeconds)
' 插入时将timestamp存入DATE字段

查询代码

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ' 获取起始日期当天0点的Unix时间戳
    Dim startTs As Integer = CInt(New DateTime(DateTimePicker1.Value.Year, DateTimePicker1.Value.Month, DateTimePicker1.Value.Day, 0, 0, 0).ToUniversalTime().Subtract(New DateTime(1970,1,1)).TotalSeconds)
    ' 获取结束日期当天23:59:59的Unix时间戳
    Dim endTs As Integer = CInt(New DateTime(DateTimePicker2.Value.Year, DateTimePicker2.Value.Month, DateTimePicker2.Value.Day, 23, 59, 59).ToUniversalTime().Subtract(New DateTime(1970,1,1)).TotalSeconds)
    
    QUERY = "SELECT * FROM [DATA] WHERE [DATE] BETWEEN @StartTs AND @EndTs"
    
    CON = New SQLiteConnection("Data Source=PERSON.sqlite;Version=3;")
    DA = New SQLiteDataAdapter(QUERY, CON)
    DA.SelectCommand.Parameters.AddWithValue("@StartTs", startTs)
    DA.SelectCommand.Parameters.AddWithValue("@EndTs", endTs)
    
    DT = New DataTable
    DA.Fill(DT)
    DataGridView1.DataSource = DT.DefaultView
    
    CON.Dispose()
End Sub

加载数据时的日期格式化

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' 将Unix时间戳转成dd.MM.yyyy格式
    QUERY = "Select [ID],[NAME], strftime('%d.%m.%Y', datetime([DATE], 'unixepoch')) as DATES From [DATA]"
    
    CON = New SQLiteConnection("Data Source=PERSON.sqlite;Version=3;")
    DA = New SQLiteDataAdapter(QUERY, CON)
    DT = New DataTable
    DA.Fill(DT)
    DataGridView1.DataSource = DT.DefaultView
    
    CON.Dispose()
End Sub

关键注意事项 #

• 必须使用参数化查询，避免SQL注入，同时避免日期格式拼接带来的错误。
• 优先使用SQLite推荐的日期存储方式：TEXT（yyyy-MM-dd格式）、INTEGER（Unix时间戳）或REAL（Julian日期），这类存储方式可以直接使用SQLite的日期函数，效率更高且不易出错。

内容的提问来源于stack exchange，提问作者alican60