SQL Date & Time Problem

[JamesRiley]

I'm having a... understandable SQL date problem... I don't believe there is anyway to correct this but I'll stil ask

I have NO problem WHATSO EVER with searching with dates as the criteria. however when I have a date and time field it gives me a error.

e.g.

I use the following ASP script
(Lets say I have already connected to the database through "Connd" )

The following script works fine

datesrch = 11/10/2003

set dateRS = Connd.Execute("Select * from Users where datejoined > " & datesrch)

But this script has a problem.

Please note that I do not declare the datesrch variable myself in the real code I retrieve it from the database in DATE format.

datesrch = 11/10/2003 12:00:00PM 'or something similar I know it definately IS a valid date

set dateRS = Connd.Execute("Select * from Users where datejoined > " & datesrch)

also does it matter that I'm using a MSAccess database but the database server engine is MySQL?

Please also Note that I have THOUSANDS OF SQL, ADO and ASP lines of code that work perfectly on my site.

Thanks for your time I will REALLY apreciate if you can help me in this query.

[MAK]

try this

set dateRS = Connd.Execute("Select * from Users where datejoined > convert(datetime,'" & datesrch & "',101)"

[JamesRiley]

Thanks I'll try it and get back to you

[JamesRiley]

Thanks for the advice but, Its not finding the convert function

undefined function "convert" in expression

What should I do from here.

Thanks for your time!

[Jim_Hazell]

James,

I think the reason the CONVERT keyword did not work is because it is a T-SQL keyword, and you are using My-SQL. I believe you need to standardise the date - in ASP, this should do it: FormatDateTime(DateValue(datesrch) + TimeValue(datesrch))

Then the SQL depends on whether you are using attached tables in Access or pastthru queries? So I'll give you a range of possiblities. Some variations to try:
Select * from Users where datejoined > #" & FormatDateTime(DateValue(datesrch) + TimeValue(datesrch)) & "#"
Select * from Users where datejoined > '" & FormatDateTime(DateValue(datesrch) + TimeValue(datesrch)) & "'"
Select * from Users where DateDiff(""n"",datejoined, #" & FormatDateTime(DateValue(datesrch) + TimeValue(datesrch)) & "#) > 0"
Select * from Users where DateDiff(""n"",datejoined, '" & FormatDateTime(DateValue(datesrch) + TimeValue(datesrch)) & "') > 0"

[JamesRiley]

Thanks alot that seems to have got it working!!!

Many Thanks

When I finish my project I'll give you a link!