Searching for dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Searching for dates

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-06-08 : 04:23:48

My date is stored in my sql table either as dd/mm/yy or (usually) as date and time dd/mm/yy/ hh:mm:ss
I have a search screen and one of the fields is the date. The user enters just the date and not the time. How do I write my sp as if the field holds the date and time there is never a match ?

Here's my sp

CREATE PROCEDURE spSL_CallSearch

@strSurname nvarchar(100),
@strTelNo nvarchar(100),
@strCallDate datetime,
@strAdd1 nvarchar(500),
@strPostCode nvarchar(10),
@strCallDesc nvarchar(2000)

AS
Select CallNo,convert(varchar(20),CallNo) + ' ' + COALESCE(Surname,'Anon') + ' :: ' + COALESCE(Add1,'No Address') + '::'+ COALESCE(Call_desc,'No details') as Ticket from tblSL_Calls
WHERE
Surname like '%'+ @strSurname+'%' and
Tel_no like '%'+@strTelNo+'%' and
Call_date like '%'+ @strCalldate+'%' and
Add1 like '%' + @strAdd1 + '%' and
Call_desc like '%'+@strCallDesc+'%'

ORDER BY CallNo desc
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-08 : 04:33:22

you'll probably need
SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-08 : 04:37:10

where datediff(day,calldate,strcalldate)=0

--------------------
keeping it simple...

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-06-08 : 04:52:49

Do you mean change

Call_date like '%'+ @strCalldate+'%' and
to

Call_date like DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
or
Call_date like datediff(day,calldate,strcalldate)=0

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-08 : 04:57:19

i mean