?
Solved

Access, SQL: how to select dates older than

Posted on 2009-07-06
8
Medium Priority
?
361 Views
Last Modified: 2012-05-07
Hi X-perts,

I need to select records with the date field older than a certain date;

"SELECT [MSCI] FROM EFdb WHERE [dates] >= 9/1/2006"

It returns ALL the records and doesn't filter the dates

What is the correct syntax for this?

Thanks
0
Comment
Question by:andy7789
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24791044
Try quoting the date. 9/1/2006 will probably be evaluated as a fractional number instead of a date.
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 2000 total points
ID: 24791093
Quotes are not the literal date delimiter ... you should use the octothorpes (#) ...

"SELECT [MSCI] FROM EFdb WHERE [dates] >= #9/1/2006#"

A note about the octothorpes delimiters, it will force Jet/Access to assume US date format if the date is in an ambiguous format, so, when creating my SQL statements via code, I will use an unabiguous formation ....

"SELECT [MSCI] FROM EFdb WHERE [dates] >= #2006-09-01#"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24791149
This is how you do it in T-SQL:
"SELECT [MSCI] FROM EFdb WHERE [dates] >= '20060901"

Or more appropriately (assuming US mdy format):
"SELECT [MSCI] FROM EFdb WHERE [dates] >= CONVERT(datetime, '9/1/2006', 101)"
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24791153
P.S. If this is not an MS SQL Server question, please refrain from adding the MS SQL Server Zone.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24791182
Good follow up acperkins ... in T-SQL the date delimiter is the single quote .... so ... I persoally still stick with International format ...

SELECT [MSCI] FROM EFdb WHERE [dates] >= '2006-09-06'
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24791352
>>Quotes are not the literal date delimiter ... you should use the octothorpes (#) ...

Since the question was in the SQL Server Zone I assumed it was a SQL Server query. I did not notice Access in the zones when I answered.
0
 

Author Comment

by:andy7789
ID: 24791398
Thank you all! Sorry for the confusion with the zones  :)
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24791443
>> Since the question was in the SQL Server Zone I assumed it was a SQL Server query <<

LOL ... I looked at the title and saw "Access" ... I didn't even see the Zone! ... :-S
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

607 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