CodingForums.com - View Single Post

Old Pedant · 10-16-2012, 08:36 PM

WRONG ANSWER

Those queries are for SQL SERVER ONLY.

This thread was about JET OLEDB queries. That is, queries into an ACCESS database.

Neither the Access nor JET drivers have CONVERT() or GETDATE() functions.

Those queries aren't even very good if you are using SQL Server.

JUST FOR EXAMPLE:

Code:

    ... WHERE column3 BETWEEN GetDate()-1 AND GetDate()

GetDate() returns the current date *AND TIME*.

So let's say you make that query at 17:00:00 (5 PM) today.

Essentilly, you are asking for

Code:

 ... WHERE column3 BETWEEN (5 PM yesterday ) AND ( 5 PM today )

So, for example, you won't find records from 11 AM yesterday.

WORSE: If column3 is a DATE-ONLY column, then you wont' find ANY records from yesterday.

I think you might want to go revisit your SQL Server documentation to find better ways than what you are doing there.

10-16-2012, 08:36 PM	PM User \| #15
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,237 Thanks: 59 Thanked 3,998 Times in 3,967 Posts	WRONG ANSWER Those queries are for SQL SERVER ONLY. This thread was about JET OLEDB queries. That is, queries into an ACCESS database. Neither the Access nor JET drivers have CONVERT() or GETDATE() functions. Those queries aren't even very good if you are using SQL Server. JUST FOR EXAMPLE: Code: ... WHERE column3 BETWEEN GetDate()-1 AND GetDate() GetDate() returns the current date AND TIME. So let's say you make that query at 17:00:00 (5 PM) today. Essentilly, you are asking for Code: ... WHERE column3 BETWEEN (5 PM yesterday ) AND ( 5 PM today ) So, for example, you won't find records from 11 AM yesterday. WORSE: If column3 is a DATE-ONLY column, then you wont' find ANY records from yesterday. I think you might want to go revisit your SQL Server documentation to find better ways than what you are doing there. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.