DateTime query ?? - 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)

DateTime query ??

Author

Topic

mauler05
Starting Member

17 Posts

Posted - 2007-10-12 : 10:53:02

Hi Guys,

Its a pretty simple query but still kinda weird when i am executing....

d_dateCreated is of datetime datatype

When i am executing following query

select ordergroup_id
from ordergroup
where user_Id = '370E7854-9014-45DC-B11E-40B139B1D169'
and d_dateCreated = '11/11/2005'

i am getting no result ....although if i am executing below query i do get lot of records...

select ordergroup_id
from ordergroup
where user_Id = '370E7854-9014-45DC-B11E-40B139B1D169'
and day(d_dateCreated) = '11' and month(d_dateCreated) = '11' and year(d_dateCreated) = '2005'

not to mention......here are few sample dates with the requested UserId that are present in OrderGroup table...

2005-11-11 14:20:36.000
2005-11-11 10:54:04.000
2005-11-11 11:13:40.000

Any suggetions ??

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-12 : 10:55:46

it's the 'time' portion of your d_datecreated. i.e. your 1st query is basically saying
where d_datecreated = 2005-11-11 00:00:00.000

and you can see from your 2nd query that is never true

Em

mauler05
Starting Member

17 Posts

Posted - 2007-10-12 : 11:00:59

Thanks :)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-12 : 11:01:27

and d_dateCreated >= '11/11/2005' and < '11/12/2005'
or
DATEADD(day,DATEDIFF(day,0,d_datecreated),0) = '11/12/2005'
or
wait for Peso to give the solution that doesn't force a table scan!

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 11:01:33

For example:

select ordergroup_id
from ordergroup
where user_Id = '370E7854-9014-45DC-B11E-40B139B1D169'
and d_dateCreated >= '11/13/2005' AND d_dateCreated < '11/14/2005'

E 12°55'05.25"
N 56°04'39.16"

mauler05
Starting Member

17 Posts

Posted - 2007-10-12 : 13:38:48

Actually the date stored in the table is 2005-11-10 17:32:46.893 and the date paramter in one of my SSSI is returning same date as 11/10/2005 05:32:46 PM

Hence when i am copmaring both these dates i m getting no rows Is there any work around for it before i compare the two dates.

Please note that i have to look for the time stamp too as i have a logic where data to be fetched is determined by the timestamp +- some n no of mins

Any suggestions ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources