dates being returned outside of query range

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 2000 Forums

Transact-SQL (2000)

dates being returned outside of query range

Author

Topic

KimJ
Starting Member

38 Posts

Posted - 2005-02-01 : 11:30:56

Hi -

Some weirdness, I think...

This query:

Select * From Event
Where CalendarDate Between ('2005-02-01 00:00:00') and ('2005-02-28 23:59:59')

Is returning this value: 2005-03-01 00:00:00 in addition to everything in February.

This is the field info for the table that data is held in...

Column_name: CalendarDate
Type: smalldatetime
Computed: no
Length: 4
Prec:
Scale:
Nullable: no
TrimTrailingBlanks:(n/a)
FixedLenNullInSource:(n/a)

Any ideas? Also, all dates are entered in the table with 00-00-00 as the time of day.

Thanks in advance,
Kim

TimS
Posting Yak Master

198 Posts

Posted - 2005-02-01 : 11:41:28

FROM BOL

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute

Change to Between ('2005-02-01 00:00') and ('2005-02-28 23:59')

Tim S

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-01 : 12:59:49

Select * From Event
Where month(CalendarDate) = 2 and year(CalendarDate)=2005

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

KimJ
Starting Member

38 Posts

Posted - 2005-02-01 : 13:02:51

Thanks to both of you for your replies - I opted to remove the milliseconds. I figured that since all the dates were entered with a time of 00-00-00 then it would be okay.

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-01 : 14:20:19

You actually have 2 issues.

If you INSERT that date into the smalldatetime field, it will round it up....and then there's the same rounding problem with the predicate


USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(CalendarDate smalldatetime)
GO

INSERT INTO myTable99(CalendarDate)
SELECT '2005-02-01 00:00:00' UNION ALL
SELECT '2005-02-10 00:00:00' UNION ALL
SELECT '2005-02-28 23:59:59' UNION ALL
SELECT '2005-02-28 23:59'    UNION ALL
SELECT '2005-03-01 00:00:00'
GO

SELECT * 
  FROM myTable99

SELECT * 
  FROM myTable99
 WHERE CalendarDate BETWEEN ('2005-02-01 00:00:00') AND ('2005-02-28 23:59:59')

SELECT * 
  FROM myTable99
 WHERE CalendarDate BETWEEN ('2005-02-01 00:00') AND ('2005-02-28 23:59')
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

BammBamm
Starting Member

9 Posts

Posted - 2005-02-01 : 21:59:43

Frankly... When I get things from the 1st of the month to the end of the month, I use greater than or equal to (>=) first of month [midnight] and less than (<) first of following month (also midnight).

I've found instances where "time of day" bites you where you least expect it. Also, it's much easier to draw parameters from first of month to first of month.

KimJ
Starting Member

38 Posts

Posted - 2005-02-02 : 10:26:28

I agree - and had I been the one to originally write the code, I would have done it differently (there's no need for the time to be in there at all!). But, since the issue has a 'quick fix' to just take of the milliseconds, I'd rather do that than rewrite the whole application.

I'm sure all this info will be very helpful with my future projects, though!

Kim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources