I have a SQL query that suppose to compare a given DateTime object to a datetime type coloumn :
SELECT COUNT(*) FROM [QuestionnairesData] WHERE [SQClientUsername] = '"+ Profile.UserName + "' AND [FillingDate] > " + Report.GetLastProductionReportDate(sqClient.SQCLIENTID)
the problem i get is on the last part of the select query. the DateTime object Report.GetLastProductionReportDate(sqClient.SQCLIENTID) gives me :
2/14/2007 15:49:04 PM
and when i delete the time part it works well but i need to check for both date and time in this case.
there is some issues with DateTime object and the datetime type in sql. any ideas ?
You need to put quotes around your datetime object:String strSQL = "" strSQL = "SELECT COUNT(*) FROM [QuestionnairesData] " + "WHERE [SQClientUsername] = '" + Profile.UserName + "' AND [FillingDate] > '" + Report.GetLastProductionReportDate(sqClient.SQCLIENTID) + "'";
Date values need to be in quotes. Or, similarly, you can do the above, but explicitly cast it to a datetime object for comparison.
[FillingDate] > CAST('12/10/2005 4:05:24 PM' AS DATETIME)
MCP - Web Based Client Development .NET 2.0
you could simply get over this by using a parameterized query. E.g specify a parameter in the sql query and then if you use SqlCommand or OleDbCommand, then populate a parameter (SqlParameter or OleDbParameter depending on the xxxCommand used) with the datetime as value.
many thanks ps2goat. those quotes worked out for me great.