peterdarazs
asked on
SQL - Dates syntax ( VB6 connecting to SQL Server database)
Hi Experts
I'm trying to update a table with new [Test Requests]. It's in VB6, connecting to a SQL database and it's designed to select tests, requested after the [lastupdate] date. Just a syntax assist on the troublesome line.
eg
LastUpdate = "21/12/2009"
'select most recent tests
rs.CursorLocation = adUseClient
Sql = ""
Sql = Sql & " Select * from REQUESTEDTESTS "
'Sql = Sql & " where convert(varchar, [requestdate],120) > " .........datevalue(LastUpd ate)??
'some examples of what i have tried
'/Sql = Sql & " where convert(varchar, [requestdate],120) > " & DateValue(LastUpdate) ' conversion failed
'/Sql = Sql & " where convert(varchar, [requestdate],120) > convert(varchar, [lastupdate],120)" 'invalid column
'/Sql = Sql & " where convert(varchar, [requestdate],120) > getdate(lastupdate)" 'doesn't work
rs.Open Sql, cn
Much appreciated.
PD
I'm trying to update a table with new [Test Requests]. It's in VB6, connecting to a SQL database and it's designed to select tests, requested after the [lastupdate] date. Just a syntax assist on the troublesome line.
eg
LastUpdate = "21/12/2009"
'select most recent tests
rs.CursorLocation = adUseClient
Sql = ""
Sql = Sql & " Select * from REQUESTEDTESTS "
'Sql = Sql & " where convert(varchar, [requestdate],120) > " .........datevalue(LastUpd
'some examples of what i have tried
'/Sql = Sql & " where convert(varchar, [requestdate],120) > " & DateValue(LastUpdate) ' conversion failed
'/Sql = Sql & " where convert(varchar, [requestdate],120) > convert(varchar, [lastupdate],120)" 'invalid column
'/Sql = Sql & " where convert(varchar, [requestdate],120) > getdate(lastupdate)" 'doesn't work
rs.Open Sql, cn
Much appreciated.
PD
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
1/ Game-master
sql = "Select * from TESTREQUESTS where cast(requestdate as dateTime) > cast('" & LastUpdate & "',as Datetime)" ' ..........Syntax error near 'as' ( i have also tried placing a space between , and the second 'as')
2/ Sharath -
I changed it to ".........RequestDate > '" & LastUpdate & "'" and IT WORKED . Grateful for the help.
siThanks
sql = "Select * from TESTREQUESTS where cast(requestdate as dateTime) > cast('" & LastUpdate & "',as Datetime)" ' ..........Syntax error near 'as' ( i have also tried placing a space between , and the second 'as')
2/ Sharath -
I changed it to ".........RequestDate > '" & LastUpdate & "'" and IT WORKED . Grateful for the help.
siThanks
try something like this...
sql = "Select * from YourTable where cast(requestdate as dateTime) > cast('" & LastUpdate & "',as Datetime)"
game-master