Dates in SQL Query

[Kenneth Keeley]

Hi,
I am trying to get the results out of an SQL database where the date is
greater than todays date.
this is thecode that I am trying to use.

SQLQuery = new SqlDataAdapter("SELECT Id, Title, Publication, InsertDate,
NoPages, Colour, Status " +
"FROM Features WHERE InsertDate >#" +
DateTime.Now.ToString("dd/MM/yyyy") +
"# AND Publication='x' OR Publication='y' ORDER BY " + strSortBy + ";",
DataBaseConnection);

All I keep getting is this error "Incorrect syntax near '#'." What am I
doing wrong. The query is copied straight from an access database query to
the same SQL Database.

Thanks for any help
Kenneth

 

[Josh]

As a Rule to avaoid problems with international dates etc I always build
my date queries like this. Not perfect but will get you past your problem.

WHERE Date_Column > convert(datetime,'01 Jan 2004')

 

[Martin Dechev]

Hi,

The general recommendation is to use sql parameters for queries - it is
safer, faster and errorproof. For examples see:

http://msdn.microsoft.com/library/e...SystemDataSqlClientSqlParameterClassTopic.asp
http://msdn.microsoft.com/library/e...taSqlClientSqlCommandClassParametersTopic.asp
http://msdn.microsoft.com/library/e...ientSqlDataAdapterClassSelectCommandTopic.asp

Hope this helps
Martin

 

[Hans Kesting]

Hi,
I am trying to get the results out of an SQL database where the date
is greater than todays date.
this is thecode that I am trying to use.

SQLQuery = new SqlDataAdapter("SELECT Id, Title, Publication,
InsertDate, NoPages, Colour, Status " +
"FROM Features WHERE InsertDate >#" +
DateTime.Now.ToString("dd/MM/yyyy") +
"# AND Publication='x' OR Publication='y' ORDER BY " + strSortBy
+ ";", DataBaseConnection);

All I keep getting is this error "Incorrect syntax near '#'." What am
I doing wrong. The query is copied straight from an access database
query to the same SQL Database.

Thanks for any help
Kenneth

The "#" syntax for dates is Access-specific, that's why you get syntax errors
on SqlServer.
Dates and strings are always difficult. As others said, it is better to use parameters,
there you can use the plain DateTime value, without string conversion.

Hans Kesting