Comparing dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing dates

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-12 : 21:35:57

I have two fields as datetime type (EventStartTime, EventEndTime). Why does this query return this:
ALTER PROCEDURE dbo.Dates

AS
BEGIN
Select Eventid, EventStartTime, EventEndTime
From EventDetails
WHERE EventDetails.EventStartTime < '12/31/2010 12:00:00 am' AND EventDetails.EventEndTime < '8/07/2011 4:51:19 PM'
END

Running [dbo].[Dates].

Eventid EventStartTime EventEndTime
----------- ----------------------- -----------------------
17 8/07/2004 11:59:00 PM 8/07/2011 4:51:19 PM
18 27/07/2007 4:00:00 PM 27/07/2008 6:01:01 PM
19 8/07/2004 11:59:59 PM 8/07/2004 11:59:59 PM
22 8/07/2008 11:59:00 PM 8/07/2008 11:59:00 PM
316 24/07/2004 2:20:30 PM 24/07/2006 2:20:30 PM
350 8/07/2004 11:59:59 PM 8/07/2011 4:51:19 PM
No rows affected.
(6 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Dates].

However this query does not return?

17 8/07/2004 11:59:00 PM 8/07/2011 4:51:19 PM

ALTER PROCEDURE dbo.Dates

AS
BEGIN
Select Eventid, EventStartTime, EventEndTime
From EventDetails
WHERE EventDetails.EventStartTime < '12/31/2010 12:00:00 am' AND EventDetails.EventEndTime = '8/07/2011 4:51:19 PM'
END

Running [dbo].[Dates].

Eventid EventStartTime EventEndTime
----------- ----------------------- -----------------------
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Dates].

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-12 : 21:41:45

I also tried with the same result?

ALTER PROCEDURE dbo.Dates

@SourceStartDate DateTime = NULL,
@SourceEndDate DateTime = NULL

AS
BEGIN
Select Eventid, EventStartTime, EventEndTime
From EventDetails
WHERE EventDetails.EventStartTime = @SourceStartDate AND EventDetails.EventEndTime = @SourceEndDate
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 00:27:19

Are EventStartTime and EventEndTime datetime fields?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-13 : 01:18:26

Yes, so I guess the problem in the first case is a datatype mismatch:) Why does it fail in the second query? where the variable is a datetime type? < and > seem to work just not =.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 02:08:51

= will not work as it takes timepart also for comparison and will only return those records who timepart will be exactly same as the one provided. Here in your case none of records match time part and so you wont get anything returned. Also you cant have = comparison with NULL. NULL is not actually stored as a value but it just designates a lack of value. so use field IS NULL instead of field=NULL. the latter will work only if you set ansi nulls option set to off.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-13 : 20:13:59

Thanks, I found the problem with = NULL should be IS NULL. I dont understand why EventDetails.EventEndTime = '8/07/2011 4:51:19 PM' does not return eventid 17 as shown above as I copied the data directly from the field so it does contain date and time. Is it the milliseconds?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources