SQL Server DateTime Query Issue

Question

I am using SqlServer Compact 3.5 (2008) and am trying to return rows that have a datetime field that is today or previous. This is the code I am trying, but it is throwing an exception. (in the Where clause)

I'm new to SQL so I'm having trouble with this one. Any ideas? If you need more data, let me know.

string selectStatement = 
  "SELECT * FROM EnglishSpanish ORDER BY AvgScore DESC " +
  "WHERE NextShow <= @Today";
SqlCeCommand selectCommand = new SqlCeCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@Today", DateTime.Today);

Answer 1

The correct Sql Syntax would be

SELECT * FROM EnglishSpanish 
WHERE NextShow <= @Today
ORDER BY AvgScor DESC

Your WHERE and ORDER BY clauses are reversed.

Also. Don't SELECT *. Even if you're selecting all of them, name your columns.

Answer 2

In lieu of the specific exception you're retrieving, try changing your query so that it's structured like this:

SELECT * FROM EnglishSpanish WHERE NextShow <= @Today ORDER BY AvgScore DESC

I think the WHERE clause has to come before the ORDER BY clause

Answer 3

The "ORDER BY" clause must come after the "WHERE" clause. The SQL statement should read

SELECT * FROM EnglishSpanish 
WHERE NextShow < @Today
ORDER BY AvgScore DESC

Also notice that I am using "<" instead of "<=". Instead of using DateTime.Today you should use DateTime.Today.AddDays(1) because DateTime.Today will give you '2010-07-29 00:00:00' which is midnight between July 28th and 29th. Hence your clause will not give you the records of today.

Answer 4

You don't need to use a parameter for this query, unless you want to support the possibility of change in the future - you can use GETDATE() to return the current datetime:

    SELECT * 
      FROM EnglishSpanish
  WHERE nextshow <= GETDATE() 
ORDER BY AvgScore DESC 

The problem was that you had the WHERE clause in the wrong spot - it's after the FROM, before the GROUP BY, HAVING, ORDER BY (in that order).