help with SQL dates
hello,
Please help with my stored proc
I need to be able to select all books not yet return for more than 30 minutes, 2 hour and 3 hours repsectively
the following is my code....it works fine but then if my current hour is 1PM...it will display the books borrowed in 12:55 PM, which should not be shown becuase it is borrowed below or less than 30 minutes....How will i check the difference between my two dates with regards to its AM/PM?
alter procedure dbo.getBooksNotReturn
(
@search nvarchar(1024) = ''
)
Select titles, isbn, dateBorrowed, status from dbo.books
where
(@search = ''
or (status <> 'Returned') and 1 =
(Case
When @search = '30m' and (datediff(hh, dateBorrowed,
CONVERT(CHAR(20), GETDATE(), 22)) > 0.5) then
1
When @search = '1h' and (datediff (hh, dateBorrowed,
CONVERT(CHAR(20), GETDATE(), 22)) > 1) then
1
When @search = '3h' and (datediff (hh, dateBorrowed,
CONVERT(CHAR(20), GETDATE(), 22)) > 3) then
1
else
0
End)
)
End
|
