DateTime Problem - 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)

DateTime Problem

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-07-14 : 10:03:02

Hello,

I have a field in my DB which is a datetime, so it has entries in there like :

13/08/2008 11:21:26
18/12/2008 09:46:11
etc

I am trying to query based on this field, to only get the entries on a particular date but it is not coming up with anything.

Obviously If I put the time in, it brings me them up, but this is no good as I don't want it specific to the second.

What syntax would I use to get round this please ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-14 : 10:08:05

[code]where datecol>=dateadd(day,datediff(day,0,your_date),0) and
datecol<dateadd(day,datediff(day,0,your_date),1) [/code]

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 10:08:25

[code]
declare @date datetime

select @date = '2008-08-13'

select *
from yourtable
where datecol >= @date
and datecol < dateadd(day, 1, @date)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-14 : 10:10:36

try

select *
from yourtable
where afield >= particulardate
and afield < DATEADD(day,1,particulardate)

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-14 : 10:11:56

I knew I should have refreshed my browser before I posted, get the yak blaster!