.Net DateTime.Now vs PostgreSQL timestamp Comparison

Question

Environment Mono, PostgreSQL, .Net MVC, Windows

I try to show all the events that happen in the future.

For this, I use the following SQL:

NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM dinners WHERE EventDate >= " + DateTime.Now, dinnerConn);

Now, if I compare DateTime.Now and my EventDate timestamp with the DB, I get the following

(EventDate) 12/18/2010 7:00:00 PM - (DateTime.Now) 10/2/2010 7:59:48 PM

They seem pretty easy to match with me, but whenever I run this query, I get the following:

ERROR: 42601: syntax error at or near "8"

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: Npgsql.NpgsqlException: ERROR: 42601: syntax error at or near "8"

Source Error: 


Line 106:           try
Line 107:           {
Line 108:               NpgsqlDataReader reader = command.ExecuteReader();
Line 109:               while (reader.Read()) 
Line 110:               {

Source File: c:\Documents and Settings\Andrew\My Documents\Visual Studio 2008\Projects\MvcApplication1\MvcApplication1\Models\DBConnect.cs    Line: 108 

Now I know that the application works differently, because if I ask him to pull out all the lunches or all the dinners that exceed a certain identifier, or a specific dinner, everything works fine, it looks like he is trying to compare timestamp with DateTime. Now.

I know this is something simple. What am I doing wrong?

Answer 1

:

NpgsqlCommand sql = db.CreateCommand();
sql.CommandType = CommandType.Text;
sql.CommandText = @"SELECT * FROM dinners WHERE EventDate >= @eventdate ;";
sql.Parameters.Add("eventdate", NpgsqlType.Date).Values = DateTime.Now;

(), .

Answer 2

, , DateTime , , Postgres.

, DateTime.ToString() . SQL , , Postgres , .

Npgsql , NUnit . :

NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM dinners WHERE EventDate >= :date", dinnerConn);
command.Parameters.Add(":date", DateTime.Now);

DateTime .NET, SELECT * FROM dinners WHERE EventDate >= now(). , , . , . , "" ( ), , .

Answer 3

Add() , AddWithValue():

NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM dinners WHERE EventDate >= :date", dinnerConn);
command.Parameters.AddWithValue(":date", new NpgsqlTypes.NpgsqlDate(DateTime.Now));