[help]按日期更正WHERE的语法 [英] [help] Correct syntax for the WHERE by date
问题描述
这是我不知道语法的部分
我希望我的表单通过使用b_date自动加载来自数据库的数据
但仅显示记录如果月份和日期匹配
不使用年份,该程序就像生日提醒
私人 Sub Form1_Load( ByVal sender As System。对象, ByVal e 作为 System.EventArgs)句柄 MyBase .Load
' TODO:这行代码将数据加载到'Bene_dbDataSet.bene_records'表中。您可以根据需要移动或删除它。
我 .Bene_recordsTableAdapter.Fill(我 .Bene_dbDataSet.bene_records)
con = 新 OleDb.OleDbConnection
dbProvider = Provider = Microsoft.Jet.OLEDB.4.0;
dbSource = 数据源= C:\Users\RedemptorisMater \Desktop\S\K..\WindowsApplication1 \ WindowsApplication1 \bene_db.mdb
如果 不 con.State = ConnectionState.Open 然后
con.Open()
结束 如果
Dim da As OleDb.OleDbDataAdapter
Dim Sql As 字符串
' '这就是部分我不知道语法,
' '我希望我的表单自动加载来自数据库的数据
' '使用b_date但仅显示记录月份和日期匹配
' '未使用年份,该计划就像一个生日提醒
Sql = SELECT * FROM bene_records
da = 新 OleDb.OleDbDataAdapter(Sql,con)
Dim dt 作为 新 DataTable
da.Fill(dt)
con.Close()
结束 Sub
请阅读我对解决方案的评论1
我建议这样查询:
PARAMETERS [mnth] INT ;
SELECT < FieldList>
FROM bene_records
WHERE b_date BETWEEN #DateSerial(年份(日期()),[mnth], 1 )# AND #DateSerial(年份(日期()),[mnth] +1,1-1)#
以上查询应返回给定月份的数据(从第一天到最后一天)。
替换< FieldList>
包含要返回的列集。如果您关心性能,请不要将*
与SELECT
语句一起使用。
要调用它,您需要设置 OledbParameter [ ^ ] vs.90%29.aspx> OledbCommand [ ^ ]。
更多:
< a href =http://www.techonthenet.com/access/functions/date/dateserial.php> DateSerial(MS Access查询) [ ^ ]
数据类型(MS Access) [ ^ ]
尝试:SELECT * FROM bene_records WHERE DATEPART(mm,GETDATE ())= DATEPART(mm,dateColumn) AND DATEPART(dd,GETDATE())= DATEPART(dd,dateColumn)
This is the part that I dont know the syntax
I want that my form automatically loads the data from the Database
by using the b_date but only display the records if there is a match in month and date
not using the year, The program is like a Birthday Reminder
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Bene_dbDataSet.bene_records' table. You can move, or remove it, as needed.
Me.Bene_recordsTableAdapter.Fill(Me.Bene_dbDataSet.bene_records)
con = New OleDb.OleDbConnection
dbProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
dbSource = "Data Source = C:\Users\RedemptorisMater\Desktop\S\K..\WindowsApplication1\WindowsApplication1\bene_db.mdb"
If Not con.State = ConnectionState.Open Then
con.Open()
End If
Dim da As OleDb.OleDbDataAdapter
Dim Sql As String
''THIS IS THE PART THAT I DONT KNOW THE SYNTAX,
''I want that my form automatically loads the data from the Database
''by using the b_date but only display the records if theres a match in month and date
''not using the year, The program is like a Birthday Reminder
Sql = "SELECT * FROM bene_records"
da = New OleDb.OleDbDataAdapter(Sql, con)
Dim dt As New DataTable
da.Fill(dt)
con.Close()
End Sub
Please, read my comment to the solution 1
I'd suggest query like this:
PARAMETERS [mnth] INT; SELECT <FieldList> FROM bene_records WHERE b_date BETWEEN #DateSerial(Year(Date()), [mnth], 1)# AND #DateSerial(Year(Date()), [mnth]+1, 1-1)#
Above query should return data for given month (from first to last day).
Replace<FieldList>
with set of columns you want to return. Do not use*
together withSELECT
statement, if you care about performance.
To call this, you need to set OledbParameter[^] for OledbCommand[^].
More:
DateSerial (MS Access query)[^]
Data Types (MS Access)[^]
Try:SELECT * FROM bene_records WHERE DATEPART(mm, GETDATE()) = DATEPART(mm, dateColumn) AND DATEPART(dd, GETDATE()) = DATEPART(dd, dateColumn)
这篇关于[help]按日期更正WHERE的语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!