sqlserver date arithmetic problem

Question

I have a records of events with starttime and endtime for a calendar control.

I want to get the events done on a particular date say 2/28/2009

But the db table has date data in form 2/28/2009 10:00:00, 2/28/2009 12:00:00.

I tried this query in a sproc created in VS 2005 IDE but it didn't work

ALTER PROCEDURE dbo.spSelectBenchEvent
(   
@EVENT_DATE DATE // BTW, THIS RETURNS ERROR :CANNOT FIND DATE DATATYPE.
                 //@EVENT_DATE HAS INVALID DATATYPE 
)
AS
BEGIN TRAN
SET NOCOUNT ON;

SELECT     ID, EID, BENCHID, PACCODE, START_TIME, END_TIME
    FROM         tbl_benchbook
    WHERE START_TIME=EVENT_DATE
    ORDER BY START_TIME

Thanks in advance.

Answer 1

Hi,

If you wish to evaluate all events on the date 3/29/2009 use the following where cluase.

SELECT *
FROM tableName
WHERE Date >= '2009/03/29' AND  Date < '2009/03/30'

The key point to take away here is that in order to capture all records that occured on a given date you need to define a date range that includes all time values for that day.

Make sense?

Cheers, John

Answer 2

There were many questions related to this one, check out:

• Floor a date in SQL server

and for best performance-wise solution check: MS SQL Date Only Without Time

Basically your code could look like:

select     
    id, eid, benchid, paccode, start_time, end_time
from  
    tbl_benchbook
where start_time >= dateadd(dd, datediff(dd, 0, @event_date), 0)
and start_time < dateadd(dd, datediff(dd, 0, @event_date)+1, 0)

Answer 3

Based on your updated ques , this should work :

   SELECT  ID, EID, BENCHID, PACCODE, START_TIME, END_TIME
   FROM    tbl_benchbook
   WHERE   START_TIME >= @EVENT_DATE 
   AND     START_TIME < DATEADD(day,1,@EVENT_DATE)
   ORDER BY START_TIME

Answer 4

--1. you need to make sure there is no time on the parameter
--2. you can just use "+1" on the datetime, to get the next day



    enter ALTER PROCEDURE dbo.spSelectBenchEvent
    (   
    @EVENT_DATE DATETIME 
    )
    AS
    BEGIN TRAN
    SET NOCOUNT ON;

    --remove any time from given date
    SET @EVENT_DATE=CONVERT(char(10),@EVENT_DATE,111)


    SELECT     ID, EID, BENCHID, PACCODE, START_TIME, END_TIME
            FROM         tbl_benchbook
            WHERE START_TIME>=@EVENT_DATE AND START_TIME<@EVENT_DATE+1
            ORDER BY START_TIME
    code here