Re: parameterized datetime query

"Jassim Rahma" <jrahma@xxxxxxxxxxx> wrote in message news:611A92B4-1226-4CE7-AA53-C42FB5BDB931@xxxxxxxxxxxxxxxx
8/22/2007 12:00:00 AM
8/22/2007 12:23:00 AM
8/22/2007 04:15:00 PM

and i want to SELECT all the 8/22/2007 dates..

how can i creates such stored procedure and make the query in C#?


One way to do it (I don't assert that it is optimal) is to select the date as
convert(datetime,convert(varchar, @TheDate, 101), 101)
This converts the date to a varchar in the format mm/dd/yyyy (dropping the time part), and back to datetime.

However, a Select done on that expression will not be able to use any indexes that Sql Server might have on that column. It is therefore better, although a little more complex, to Select ... Where column Between @startdate and @enddate, where you would build startdate and enddate in your C# code to be the desired date at 00:00:00 and the desired date at 23:59:59, respectively. Your code would be similar to the following:

DateTime dateToSearch = DateTime.Now; //Test value
DateTime dateWithoutTime = dateToSearch.Date;
DateTime startDate = dateWithoutTime;
DateTime endDate = dateWithoutTime.AddSeconds(24*3600-1);
string query = "Select * from TheTable Where TheColumn Between @startDate And @endDate";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
SqlDataReader rdr = cmd.ExecuteReader(); //For instance



.

Relevant Pages
Loading