parameterized datetime query

  • Thread starter Thread starter Jassim Rahma
  • Start date Start date
J

Jassim Rahma

Hi,

I want to know how can i query from sql server date field without the time?
i mean if i have the following dates:

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#?
 
Sql-specific questions like this should be posted to:

microsoft.public.sqlserver.programming

-HTH
 
Jassim Rahma said:
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
 
AddSeconds(24*3600-1);
....
Between @startDate And @endDate

Just a very minor addition to a good and complete answer; a much
easier option (safer too, if you need that last second) is to just
AddDays(1), and use "(TheColumn >= @startDate AND TheColumn <
@endDate)". Can also be done at the SQL end by DATEADD.
convert(datetime,convert(varchar, @TheDate, 101), 101)
To strip the time, a more efficient approach (which OK, uses a but
more of the underlying implementation details) is to simply cast to
int and back - or cast to float and take the floor(); the numeric
representation of a datetime is as the fractional number of days into
the epoch, hence midnight is {some integer}.0, midday is {some
integer}.5, etc.

I'll shut up now, as we left C#-ville some time ago ;-p

Marc
 
Back
Top