Access, SQL: how to select dates older than

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
andy7789Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
datAdrenalineConnect With a Mentor Commented:
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
 
mrjoltcolaCommented:
Try quoting the date. 9/1/2006 will probably be evaluated as a fractional number instead of a date.
0
 
Anthony PerkinsCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
P.S. If this is not an MS SQL Server question, please refrain from adding the MS SQL Server Zone.
0
 
datAdrenalineCommented:
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
 
mrjoltcolaCommented:
>>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
 
andy7789Author Commented:
Thank you all! Sorry for the confusion with the zones  :)
0
 
datAdrenalineCommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.