我要在到期日前仅剩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子句中添加所有已支付的款项,这将进一步减少行数