DateTime Question?

Hi All!

I have a SP that does a search in a table. I have a datetime field. But I would like to only search by date, not by datetime. I noticed it works only if the time is 12:00am. But if it's different, the date won't show. I do need to display the date and time.

here is my SP

PROCEDURE [dbo].[SearchHist]

-- Add the parameters for the stored procedure here

@.FName nvarchar(50),

@.LName nvarchar(50),

@.DOB nvarchar(50),

@.Acct nvarchar(50),

@.Login nvarchar(50),

@.Status nvarchar (50),

@.Rmark nvarchar(255),

@.Room nvarchar (50),

@.Age nvarchar(50),

@.Sdate datetime,

@.Type nvarchar(50),

@.Misc bit

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

If LEN(@.FName)>0 OR LEN(@.LName)>0 OR LEN(@.DOB)>0 OR LEN(@.Acct)>0 OR

LEN (@.Rmark)>0 OR LEN (@.Room)>0 OR LEN (@.Age)>0 OR LEN(@.Sdate)>0

OR LEN (@.Type)>0 OR LEN (@.Misc)>0 OR (@.Login)>0

-- Insert statements for procedure here

SELECT Archive_Orders.First_Name, Archive_Orders.Last_Name, Archive_Orders.Account_Number, Archive_Orders.Remarks, Locations.Loct_Desc,

Archive_Orders.Rm_Desc, Archive_Orders.Age, Archive_Orders.Service_Date_Time, Archive_Orders.Type, Archive_Orders.Order_ID,

Order_Status.Status, Archive_Orders.Misc

FROM Archive_Orders INNER JOIN

Locations ON Archive_Orders.Location_ID = Locations.Location_ID INNER JOIN

Order_Status ON Archive_Orders.Status_ID = Order_Status.Status_ID

WHERE (Archive_Orders.First_Name = @.FName) OR

(Archive_Orders.Last_Name = @.Lname) OR

(Archive_Orders.DOB = @.DOB) OR

(Archive_Orders.Account_Number = @.Acct) OR

(Order_Status.Status = @.Status) OR

(Archive_Orders.Remarks = @.Rmark) OR

(Archive_Orders.Rm_Desc = @.Room) OR

(Archive_Orders.Age = @.Age) OR

(Archive_Orders.Service_Date_Time = @.SDate) OR

(Archive_Orders.Type = @.Type) OR

(Archive_Orders.Misc = @.Misc) OR

(Archive_Orders.Login = @.Login)

END

TIA!

Rudy

If you're looking to return Orders for a particular day, i tend to use DATEDIFF as its quick and simple.

eg

WHERE DATEDIFF(D, Archive_Orders.Service_Date_Time, @.SDate) = 0

HTH!

|||

There are multiple approaches to searching dates. You could for every row strip off the time value, using a construct like for example:

Code Snippet

convert( datetime, convert(varchar(10), your_date_col, 120), 120)

and then check for equality with the value you are looking for. This has a big disadvantage though when searching historical data which tend to contain many records: you're asking sql server to first convert every value in the table and then evaluate all of those converted values against your selection criterium, i.e. it will be slow.

The better approach is to select all datetime values within the range from your_date 0:00 and before your_date + 1 0:00. Thus your where clause will look something like:

Code Snippet

where your_datetime_col >= @.your_search_date

and your_datetime_col < dateadd( day, 1, @.your_search_date)

This way SQL can more easily answer your query.

You may also want to ensure in your sp (depending on your requirements) that the @.your_search_date value does not have a time component in it by adding something like this before your select statement:

Code Snippet

select @.your_search_date = convert( datetime, convert(varchar(10), @.your_search_date, 120), 120)

|||

Of the methods mentioned, I like this one (mentioned by rrozema) better:

Code Snippet

where your_datetime_col >= @.your_search_date

and your_datetime_col < dateadd( day, 1, @.your_search_date)

with this method your search can take advantage of a date index -- provided that one is available -- and use a seek. The other methods are likely to scan.

|||

Here's how I do a Date search for a SINGLE day. Let's assume you're looking for all records where occurs any time on August 9, 2007 (i.e. '8/9/2007 00:00:00' to '8/9/2007 23:59:59.999')

Code Snippet

declare @.TargetDay int

set @.TargetDay = datediff(d,0,'09 August 2007')

select *

from <MyTable> where DateDiff(d,0, <MyDate>) = @.TargetDay

|||

@.rusag2: Your solution will most likely do a scan too. i.e. If there are many of rows in the table, this is going to be very slow as SQL can not take advantage of indexes (given that any are available).

|||

Absolutely agreed. If the column is indexed, you might take advantage of that like this:

Code Snippet

declare @.dtStart datetime, @.dtEnd datetime

set @.dtStart = '09 Aug 2007'

set @.dtEnd = dateadd(d,1,@.dtSTart)

set @.dtEnd = dateadd(ms, -1, @.dtEnd)

select * from <MyTable> where <DateField> between @.dtStart and @.dtEnd

|||

There are a couple of other potholes that you need to be aware of. My understanding here is that we are in the context of a stored procedure and that the date is a parameter of the stored procedure. In the context of a stored procedure, a where clause such as this:

Code Snippet

where your_datetime_col >= @.your_search_date
and your_datetime_col < dateadd( day, 1, @.your_search_date)

has an advantage over a where clause in which you load values into a second variable like this:

Code Snippet

declare @.dtStart datetime, @.dtEnd datetime
set @.dtStart = '09 Aug 2007'
set @.dtEnd = dateadd(d,1,@.dtSTart)
set @.dtEnd = dateadd(ms, -1, @.dtEnd)
select * from where between @.dtStart and @.dtEnd

because the first where clause can take advantage of parameter sniffing and has a better chance of an index seek than the second query.

Also, the upper bound of the first where clause uses a "strictly less than" comparator whereas BETWEEN uses a "less than or equal to" comparator for the upper bound. To compensate you need to STILL include the less than operator like:

Code Snippet

select * from <MyTable> where <DateField> between @.dtStart and @.dtEnd

and <DateField> < @.dtEnd

|||

Thank you all!!

Great suggestions!!

(Archive_Orders.Service_Date_Time >= @.SDate

and Archive_Orders.Service_Date_Time < dateadd(day, 1, @.SDate))

This one worked very nicely. Thanks rrozema!!

Rudy