Date Conversion help - 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)

Date Conversion help

Author

Topic

Netjunkie
Starting Member

17 Posts

Posted - 2009-03-14 : 02:46:54

I need to pull certain records from a table for a given date.
I get some records when I use the '>=' operator but nothing when i use '='.

Here are the 2 cases

select e.empid,(e.eventime) as "InTime" from events e
where e.eventime >= '2009-03-06'
and e.empid = 98

Results

98 "2009-03-06 04:49:41.000"
98 "2009-03-06 05:18:55.000"
98 "2009-03-06 14:07:55.000"
98 "2009-03-06 16:02:50.000"
98 "2009-03-06 16:25:05.000"

select e.empid,(e.eventime) as "InTime" from events e
where e.eventime = '2009-03-06'
and e.empid = 98

No records

Datatype of eventime is datetime.
I tried converting them to 101 date format but that didnt help.

Thanks
Netjunkie

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-14 : 02:56:37

try like this
select e.empid,(e.eventime) as "InTime" from events e
where dateadd(d,datediff(d,0,e.eventime),0) = '2009-03-06'
and e.empid = 98

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-14 : 03:41:51

bklr, please stop advicing your example. It is not a godo practice and will prohibit sql server to use an index (if exists) over eventtime column. We have asked you to stop doing this several times.

select empid, eventime as InTime
from events
where eventime >= '2009-03-06'
and eventime < '2009-03-07'
and empid = 98

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources