CODEWITHSUNDEEP
c#sql-serverdatetimesql-server-ce
Awaken
Awaken

Reputation: 1253

SQL Server DateTime Query Issue

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

Upvotes: 0

Views: 997

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332731

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

Upvotes: 1

Jakob Christensen
Jakob Christensen

Reputation: 14956

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.

Upvotes: 4

Rob
Rob

Reputation: 45789

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

Upvotes: 2

AllenG
AllenG

Reputation: 8190

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.

Upvotes: 3

Related Questions