Link to home
Start Free TrialLog in
Avatar of Srinivas Mantha
Srinivas ManthaFlag for India

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_code,ep.spec_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_code,ep.spec_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
SOLUTION
Avatar of HugoHiasl
HugoHiasl

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Srinivas Mantha

ASKER

I recoded to give textbox date output in the format you suggested i.e. dd-mm-yyyy.  For example, today the output is 17-04-2010.  I just copied your SQL and pasted.  
It give me an error with a message
Quoted string not properly terminated. Can it be rectified
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the prompt reply