smalldatetime and between - 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 2000 Forums

Transact-SQL (2000)

smalldatetime and between

Author

Topic

matty1stop
Starting Member

37 Posts

Posted - 2005-04-25 : 13:50:07

When I run the following query against my table it returns records where the mySmallDateTime field's value is 04/09/2005 00:00:00

It seams that this would be out of the bounds of the query. Can anyone help me understand why the record with the date of 4/9/2005 would appear in this result set?

select *
from myTable
where mySmallDateTime between '04/02/2005 00:00:00'
and '04/08/2005 23:59:59'

This query returns the same results
select * from hoursworked
where empid in ('MH44318')
and timein >= '04/02/2005 00:00:00'
and timein <='04/08/2005 23:59:59'

Thanks for your help,

Matt

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-25 : 14:06:19

What does this five you

SELECT CONVERT(varchar(25),mySmallDatetime,126)
FROM....

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-25 : 14:10:22

Your second date is getting rounded off to midnight. This shows what is happening,

select MyDate = convert(smalldatetime,'04/08/2005 23:59:59' )

MyDate                                                 
------------------------------------------------------ 
2005-04-09 00:00:00

(1 row(s) affected)

You should change your query to use the less than operator. This is usually the best way to specify a date range in SQL Server. Also, you should code text dates in yyyy/mm/dd format, because it works with all SQL Server default date formats.

select *
from
	hoursworked
where
	empid in ('MH44318')		and
	timein >= '2005/04/02 00:00:00' 
	timein <  '2005/04/09 00:00:00'

CODO ERGO SUM

matty1stop
Starting Member

37 Posts

Posted - 2005-04-25 : 14:12:54

This is the value for the record that I don't understand why is in the result set.

2005-04-09T00:00:00

matty1stop
Starting Member

37 Posts

Posted - 2005-04-25 : 14:16:36

OK, thanks for the information and the advice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources