Srinivas Mantha
asked on
dynamic oracle sql syntax for date greater than today
I am using frontend traditional visual basic 6 and backend oracle database
I want write an SQL for oracle to return a recordset with records greater than a defined date
The following static date type is working well
rspaperlist.Open "select ex.exam_code,ex.exam_name, ex.exam_date,ep.paper_code ,ep.bank_c ode,ep.spe c_code from qb_m_exams ex,qb_m_exampapers ep where ex.exam_code=ep.exam_code and ep.spec_code=2 and ep.bank_code=1 and ex.exam_date >= DATE '2010-04-16'", cnora, 1, 1
To make the SQL more dynamic, I created a code to return a date what oracle expects in its SQL
Dim tempday, tempmon
tempday = Day(Date)
If Len(tempday) = 1 Then
tempday = "0" & tempday
End If
tempmon = Month(Date)
If Len(tempmon) = 1 Then
tempmon = "0" & tempmon
End If
oradttoday = Year(Date) & "-" & tempmon & "-" & tempday
Me.txttoday = oradttoday
In the form, a textbox named txttoday contains current date as required by oracle.
The following code is not working
rspaperlist.Open "select ex.exam_code,ex.exam_name, ex.exam_date,ep.paper_code ,ep.bank_c ode,ep.spe c_code from qb_m_exams ex,qb_m_exampapers ep where ex.exam_code=ep.exam_code and ep.spec_code=2 and ep.bank_code=1 and ex.exam_date >= DATE & " '" & me.txttoday &"'"", cnora, 1, 1
How should the SQL syntax be modified
I want write an SQL for oracle to return a recordset with records greater than a defined date
The following static date type is working well
rspaperlist.Open "select ex.exam_code,ex.exam_name,
To make the SQL more dynamic, I created a code to return a date what oracle expects in its SQL
Dim tempday, tempmon
tempday = Day(Date)
If Len(tempday) = 1 Then
tempday = "0" & tempday
End If
tempmon = Month(Date)
If Len(tempmon) = 1 Then
tempmon = "0" & tempmon
End If
oradttoday = Year(Date) & "-" & tempmon & "-" & tempday
Me.txttoday = oradttoday
In the form, a textbox named txttoday contains current date as required by oracle.
The following code is not working
rspaperlist.Open "select ex.exam_code,ex.exam_name,
How should the SQL syntax be modified
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the prompt reply
ASKER
It give me an error with a message
Quoted string not properly terminated. Can it be rectified