> it's datetime. Below is my sproc that works fine with m/d/yyyy
Yes, but it MIGHT NOT TOMORROW! Use a *SAFE* format, please. YYYYMMDD
works for SQL Server,YYYY-MM-DD works for Access. Please read
http://www.aspfaq.com/2023 -- just because something works fine does not
make it the right thing to do.
now, if i add the time to the m/d/yyyy value in my database, my sproc
won't recognize them.
What do you mean? Do you get an error message? If so, what is it?
SELECT *
FROM t_events
WHERE eventArchived = 0 AND
eventStart_date >= CONVERT(datetime,(convert(varchar,@dtDate,101) + '
12:00 AM')) AND
eventEnd_date <= CONVERT(datetime,(convert(varchar,@dtDate,101) + ' 11:59
PM'))
Do not use SELECT * in production code. And try the following instead, if
you look atthe queyr plan I think you will find that it is at least as
efficient if not more so, and is much easier to read.
-- always name the owner!
CREATE PROCEDURE dbo.mlc_CALENDAR_GetDayEvents
@dt SMALLDATETIME
AS
BEGIN
SET NOCOUNT ON
-- most efficient way to strip time:
SET @dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @dt))
SELECT Column_Names
FROM t_events
WHERE eventArchived = 0
AND eventStart_date >= @dt
AND eventEnd_date < (@dt+1)
-- use less then the next day at midnight
-- then you don't lose values like 11:59:30
END
GO
Now, do you really mean to only capture events that last < 24 hours? Or did
you mean to capture events that start on @dt, end on @dt, or are in progress
on @dt?
A
