0

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);
Improve this question
3
  • The specific exception you're receiving would be useful to diagnose this =) Commented Jul 29, 2010 at 15:01
  • That said, I think both answers that are regarding query order (i.e. WHERE needs to be before ORDER BY) are the correct answer to your problem =) Commented Jul 29, 2010 at 15:03
  • Thanks everyone for helping me solve this so quickly. I appreciate it. Commented Jul 29, 2010 at 15:23

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for the extra info. In my case, I only write things using DateTime.Today and additions to that, so all of my SQL Dates should be on the midnight time, but I'll definitely look at my data and make sure it is behaving as expected.
3

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.

Improve this answer

Comments

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

Improve this answer

Comments

1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.