Help with 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

Other Forums

MS Access

Help with query

Author

Topic

dkeener635
Starting Member

6 Posts

Posted - 2007-06-19 : 19:37:57

I have a table with the following columns

State JobType WrStat CommitDateTime
GA IBADSS PENDING 6/19/2007 8:00:00 PM
GA IBADSS PENDING 6/19/2007 8:00:00 PM
GA IBADSS PENDING 6/19/2007 8:00:00 PM
GA IBADSS PENDING 6/19/2007 8:00:00 PM
GA IBADSS PENDING 6/20/2007 8:00:00 PM
GA IBADSS PENDING 6/20/2007 8:00:00 PM
GA IBADSS PENDING 6/21/2007 8:00:00 PM
GA IBADSS PENDING 6/21/2007 8:00:00 PM
GA IBADSS PENDING 6/21/2007 8:00:00 PM

All columns are defined as text with the exception of the CommitDateTime which is defined as Date/time

I wrote the following query which will return a data count until I try to query with the date at which time I get no results.

This is the query that I have:

SELECT tblDATA_IDS1.State AS Sector, Count(tblDATA_IDS1.JobType) AS [Total Sync at NID]
FROM tblDATA_IDS1
WHERE (((tblDATA_IDS1.JobType) In ("ibaddo","ibadps","ibadsp","ibadss","iradps","iradss")) AND ((tblDATA_IDS1.WrStat) In ("assign","dispatch","pending")) AND ((tblDATA_IDS1.CommitDateTime)=Date()))
GROUP BY tblDATA_IDS1.State
HAVING (((tblDATA_IDS1.State)="GA"));

I would expect this query to return a count of "4" as today's date is 6/19/2007. I would also expect that if I changed the query to be
((tblDATA_IDS1.CommitDateTime)=Date()+1)), that I would get a return count of "2" for the two records of 6/20/2007 (todays date +1), and I would also expect that if I changed it to ((tblDATA_IDS1.CommitDateTime)>Date+1())), that I would get a return count of "3" for the three records of 6/21/2007.

If I take the date function out of this query, it returns a count of "9" records as all records meet the other criteria. When I put any of the date queries back in, I get no record counts returned.

Any help with this would be greatly appreciated.

David

nheidorn
Starting Member

28 Posts

Posted - 2007-06-20 : 15:28:57

The Date() function only returns the date. That is, no time is returned. This causes your query to compare Midnight on the current date to the CommitDateTime field.

There are several ways to handle this. In your case, I would probably change the query to:

SELECT tblDATA_IDS1.State AS Sector, Count(tblDATA_IDS1.JobType) AS [Total Sync at NID]
FROM tblDATA_IDS1
WHERE (((tblDATA_IDS1.JobType) In ("ibaddo","ibadps","ibadsp","ibadss","iradps","iradss"))
  AND ((tblDATA_IDS1.WrStat) In ("assign","dispatch","pending"))
  AND ((tblDATA_IDS1.CommitDateTime)>=Date()) 
  AND ((tblDATA_IDS1.CommitDateTime)<DateAdd('d', 1, Date())))
GROUP BY tblDATA_IDS1.State
HAVING (((tblDATA_IDS1.State)="GA"));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources