Retrieve current date records from oracle database using vb6
I have one table that contains a list of works. So I want to display the today's work list in msflexgrid using vb6.
code:
strwrlist = "Select * From BIOMED.HelpDesk_Work_开发者_如何学GoMaster where TO_DATE(TO_CHAR(WR_DATE,'DD-MON-YYYY HH:MM:SS'),'DD-MON-YYYY HH:MM:SS')='" & dt & "'"
'dt hold the current date i.e. dt = Format(CDate(rsgetdt.Fields("SYSDATE")), "DD-MMM-YYYY HH:MM:SS")
MsgBox strwrlist
Set rsgetwrlist = Nothing
If rsgetwrlist.State = adStateOpen Then rsgetwrlist.Close
rsgetwrlist.Open strwrlist, Cn.con, adOpenForwardOnly, adLockReadOnly
Do While Not rsgetwrlist.EOF
If IsNull(rsgetwrlist("WR_NO")) = False Then
msflxgrdlow.TextMatrix(r, 0) = rsgetwrlist.Fields("WR_NO")
End If
msflxgrdlow.AddItem ""
rsgetwrlist.MoveNext
r = r + 1
loop
But it does not give me a list of records when I run this query from vb and same query when I run in toad (oracle) it gives the list of work. So what is the exact problem?
I expect part of it is you coersion between verious different formats for the date comparison. Try just:
strwrlist = "Select * From BIOMED.HelpDesk_Work_Master where WR_DATE=TIMESTAMP'" & format(dt, "yyyy-mm-dd hh:mm:ss") & "'"
making sure that both WR_DATE and the dt are properly Date typed (TIMESTAMP in Oracle).
This preserves and does the proper date conversion and comparison.
精彩评论