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.

there are only datetime and smalldatetime datatypes relating to date. Use them or use event_date as varchar. — Learning
– Learning, Commented Mar 3, 2009 at 12:35
About the datatype error. You need to change date to datetime if you are using SQLServer2005 or earlier — kristof
– kristof, Commented Mar 3, 2009 at 12:36

Community · Accepted Answer · 2017-05-23 11:59:08Z

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)

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Mar 3, 2009 at 12:07

kristof

54k24 gold badges89 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Learning · Accepted Answer · 2009-03-03 12:44:27Z

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

edited Mar 3, 2009 at 12:44

answered Mar 3, 2009 at 12:20

Learning

8,2133 gold badges39 silver badges47 bronze badges

2 Comments

Hobo Over a year ago

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.

kk. Over a year ago

@All, i have a new problem, date is not getting recognised as a valid datatype.

John Sansom · Accepted Answer · 2009-03-03 15:16:30Z

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

edited Mar 3, 2009 at 15:16

answered Mar 3, 2009 at 11:50

John Sansom

42k9 gold badges74 silver badges87 bronze badges

6 Comments

Learning Over a year ago

btw : just noticed that it would indeed be a great Feb if it had 30 days :)

cjk Over a year ago

With this method, you obviously can't just add a day to the day part of the date :)

John Sansom Over a year ago

@Learning: Updated just for you Tipex ;-)

John Sansom Over a year ago

@Learning: My code is oh so efficient that it can actually bend time and space.

Learning Over a year ago

John : Ha! I suspected that ... that I pointed to it was just a blip in that space time continuum. Sorry. :)

|

KM. · Accepted Answer · 2009-03-03 13:58:51Z

-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

answered Mar 3, 2009 at 13:58

KM.

104k34 gold badges183 silver badges214 bronze badges

2 Comments

StingyJack Over a year ago

Casting a date (numeric) to a char is much slower than just leaving it numeric.

KM. Over a year ago

it only converts it one time, hardly a cpu killer!

Collectives™ on Stack Overflow

sqlserver date arithmetic problem

4 Answers 4

Comments

2 Comments

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related