Need to write a query for MS SQL to select all records after a specific date
Hello Experts!
I need to write a query that will be delivered via ASP to a MS Access table.
This is what I have tried:
select ordernumber from logsearch where ordernumber='2n2222' AND searchdate>11/1/2014
The field searchdate is setup as a datetime field.
The query does not discriminate by date.
I've tried surrounding the date with #, ', and " to no avail.
What am I missing?
I need to write a query that will be delivered via ASP to a MS Access table.
This is what I have tried:
select ordernumber from logsearch where ordernumber='2n2222' AND searchdate>11/1/2014
The field searchdate is setup as a datetime field.
The query does not discriminate by date.
I've tried surrounding the date with #, ', and " to no avail.
What am I missing?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both of you, we are getting close.
I apologize for my error.. I code some much in Access... this is NOT Access.
I'm running classic ASP and trying to write to an SQL database.
LordWabbit seems to be right on... I've determined that ASP is using the American time format, while the server seems to be using a different format with the year first
So what do I do to fix the problem? This gets more interesting the more I think about it.
Hmmmm... if someone in Europe searches on my website would date() return his date and time, or the date and time of the server which is in the US? I suspect all times will be based upon the local time of the server. Is this correct?
If so, the only issue should be getting the server to store in the same format, but I don't know how to do this. Would this be a database setting that I can control, or a server setting that's locked up behind the admins door?
Any thoughts?
I apologize for my error.. I code some much in Access... this is NOT Access.
I'm running classic ASP and trying to write to an SQL database.
LordWabbit seems to be right on... I've determined that ASP is using the American time format, while the server seems to be using a different format with the year first
So what do I do to fix the problem? This gets more interesting the more I think about it.
Hmmmm... if someone in Europe searches on my website would date() return his date and time, or the date and time of the server which is in the US? I suspect all times will be based upon the local time of the server. Is this correct?
If so, the only issue should be getting the server to store in the same format, but I don't know how to do this. Would this be a database setting that I can control, or a server setting that's locked up behind the admins door?
Any thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you everyone.
My mistake was that I forgot the single quotes.
Surprising, I did not get an error. It just gave me the wrong query results.
dsacker showed me the solution first, but it wasn't until I read Scots message that I figured out my mistake.
Thanks
My mistake was that I forgot the single quotes.
Surprising, I did not get an error. It just gave me the wrong query results.
dsacker showed me the solution first, but it wasn't until I read Scots message that I figured out my mistake.
Thanks
Dates are always a pain to work with when different time zones and date formats are involved. If you stick to ISO 8601 you should have a lot less problems. Access stores dates internally in MM/dd/YYYY and then converts the dates based on local settings. We have an old legacy system using access and every now and then (about once a year) the day and month will be swapped around (where applicable). Very annoying. If you can get off access, there are data import tools in SQL Server where you can import a access database into SQL Server in a couple of mouse clicks, not sure if it's applicable in your case however, but something to keep in mind.