我要在到期日前仅剩5天的情况下,在客户状态下将以下内容显示为到期日。
我从互联网上搜索了很多内容,但我仍然迷失了..
我尝试了不同的查询,但仍然没有得到我想要的

这是我的代码:

Public Sub fillNearDue()

    Dim dbDataSet As New DataTable
    Dim sda As New MySqlDataAdapter
    Dim bSource As New BindingSource
    Dim todayD As String = DateTime.Now.ToString("yyyy-dd-MM")
    connect()

    'Dim cmd1 As New MySqlCommand("SELECT *, IF(now() > dueDate, 'EXPIRED', '') AS Expiry FROM payments", con)
    '  Dim cmd1 As New MySqlCommand("if(dueDate-now()*-1 <= 5, 'EXPIRED','') AS Expiry FROM payments", con)
    'Dim cmd1 As New MySqlCommand("SELECT *  FROM payments WHERE TIMESTAMPDIFF(day,'dueDate',CURRENT_TIMESTAMP()) < 1", con)
    Dim cmd1 As New MySqlCommand("Select *, DATEDIFF(dueDate,now() ) as Days from payments where DATEDIFF( dueDate,now()) < 5", con)
    sda.SelectCommand = cmd1


    sda.Fill(dbDataSet)
    bSource.DataSource = dbDataSet
    GRDnearDue.DataSource = bSource

    disconnect()

End Sub

最佳答案

您的选择将为您提供5天内到期的所有pymenst,以及今天已到期的所有付款。因此,将DATEDIFF天减少为仅正数,您将获得所需的所有数据。

尝试

Dim cmd1 As New MySqlCommand("Select *, DATEDIFF(dueDate,now() ) as Days
     from payments where DATEDIFF( dueDate,now()) >= 0 AND DATEDIFF( dueDate,now()) <= 5", con)


您可以在where子句中添加所有已支付的款项,这将进一步减少行数

10-06 06:28