1

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
kk.
  • 667
  • 1
  • 15
  • 33
  • there are only datetime and smalldatetime datatypes relating to date. Use them or use event_date as varchar. – Learning Mar 03 '09 at 12:35
  • About the datatype error. You need to change date to datetime if you are using SQLServer2005 or earlier – kristof Mar 03 '09 at 12:36

4 Answers4

2

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

John Sansom
  • 41,005
  • 9
  • 72
  • 84
2

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

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)
Community
  • 1
  • 1
kristof
  • 52,923
  • 24
  • 87
  • 110
2

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
Learning
  • 8,029
  • 3
  • 35
  • 46
  • Much clearer, but you'll pick up any start times with a time of midnight the following day ("between" is inclusive). I think you'll need >= and <. You'll also need @EVENT_DATE, not EVENT_DATE. – Hobo Mar 03 '09 at 12:39
  • @All, i have a new problem, date is not getting recognised as a valid datatype. – kk. Mar 03 '09 at 12:42
-1
--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
KM.
  • 101,727
  • 34
  • 178
  • 212