asked on

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

mrjoltcola

Try quoting the date. 9/1/2006 will probably be evaluated as a fractional number instead of a date.

ASKER CERTIFIED SOLUTION

datAdrenaline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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)"

Anthony Perkins

P.S. If this is not an MS SQL Server question, please refrain from adding the MS SQL Server Zone.

datAdrenaline

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'

mrjoltcola

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

andy7789

ASKER

Thank you all! Sorry for the confusion with the zones :)

datAdrenaline

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