Date / Time Stamp - 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)

Date / Time Stamp

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-05-07 : 10:24:58

Hi,

I have a problem with a date field. It is of type smalldatetime. When I run the query shown below it does not return the correct results, it actually appears to ignore the date values altogether:

select toro_clientid, max("date")
from transactions
group by toro_clientid
having max("date") >=17/06/2002

I would expect this to return all those toro_clientid's who had a maximum date of >= 17/06/2002, but it actually returns dates previous to the date stipulated above. As a test I stripped off the timestamp part of the field and created a new field which contains only the date data. I then ran the query on the new field and the same problem occurs. As a final test I exported the data and imported it into Microsoft Access, I then set the data type of the date field to date ran the query and it works fine.
Why is this query not taking any notice of the date constraint?

Cheers

Paul

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-07 : 10:37:12

USE ISO Format yyyymmdd

select toro_clientid, max("date")
from transactions
group by toro_clientid
having max("date") >= '20020617'

What does this return

SELECT toro_clientid, date
FROM transactions
WHERE date >= '20020617'
ORDER BY toro_clientid Asc, date Desc

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-05-07 : 11:22:18

Hi Valterborges,

Thanks for your reply. If I run your first query on the date field which I stripped the time stamp off, then the query works great. If I run it on the original field it returns 0 records. If I run your second query on the ammended field it appears to return all dated for all records. If i run it on the original field then again it returns 0 records.

It looks like the time stamp is affecting the query somehow????

Cheers

Paul

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-07 : 23:03:58

post your ddl and some of the data and we can play.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources