Solved

Need to write a query for MS SQL to select all records after a specific date

Posted on 2014-11-23
5
387 Views
Last Modified: 2014-11-23
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?
0
Comment
Question by:pcalabria
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 250 total points
ID: 40460500
You've labelled this question under SQL Server, although your question mentions Access. Are you looking for a query using the syntax for SQL Server or the syntax for Access?

If SQL Server, a date with the single quotes should work, perhaps as follows:

select ordernumber from logsearch where ordernumber = '2n2222' AND searchdate >= '2014-11-01'

If Access, perhaps this might work:

select ordernumber from logsearch where ordernumber = '2n2222' AND searchdate >= #2/2/2006#
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 40460510
Access usually requires the # but if you are using odbc drivers that no longer applies.  Also it would solve a lot of localization issues if you used ISO 8601 date format which is year month day (yyyy-DD-mm).  From your code snippet it seems that you are using american date format mm/DD/yyyy which could cause multiple issues if the target access database was on a machine set to a different date format.  To test this you could try sending a date format where the month would be out of range if it was being interpreted as day instead of month eg.  11/13/2014, this would break on a machine set to dd/MM/yyyy but would be legal on a machine set to MM/dd/yyyy.  

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.
0
 

Author Comment

by:pcalabria
ID: 40460533
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?
0
 
LVL 53

Accepted Solution

by:
Scott Fell,  EE MVE earned 250 total points
ID: 40460584
If you are building your sql statement in asp/vb, you are still submitting it as sql server.  So the dates should have a single quote.  You can use
sql="select ordernumber from logsearch where ordernumber='2n2222' AND searchdate > '11/1/2014' "

Open in new window


Now your other  problem is how to get the date to the right format.  You want to get the date in the format of whatever sql server is set to.   Even if sql server is in the default format of 2014-11-22 you will be able to search using the code above.  

But you can also convert the date in your sql.  As example using this cheat sheet http://www.sql-server-helper.com/tips/date-formats.aspx to convert to 4 digit year British/French

sql="select ordernumber from logsearch where ordernumber='2n2222' AND searchdate > CONVERT(VARCHAR(10), '11/1/2014', 103) "

Open in new window

0
 

Author Closing Comment

by:pcalabria
ID: 40460970
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
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question