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

General SQL Server Forums

New to SQL Server Programming

Help with query

Author

Topic

dkeener635
Starting Member

6 Posts

Posted - 2007-06-19 : 18:56:35

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

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-06-19 : 19:11:12

Since the time stamp of the two dates might differ, the query is not fetching any results.

You can use Datediff for this purpose -

datediff(day,tblDATA_IDS1.CommitDateTime, getdate()) = 0

BTW what is DATE() used in the query?

dkeener635
Starting Member

6 Posts

Posted - 2007-06-19 : 19:18:53

thanks cvraghu. If I use datediff, I assume that would return today's results? How would I return tomorrow seperately and any future dates beyond tomorrow seperately? The reason I used DATE() is because I thought that would return or fetch today's date??? I'm really just interested in the "date" portion of the data to be queried on as I realize that the datetime datatype will always be date and time. Would it be possible to use cDate() or some other function to test the date in the query to return the count?

David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-19 : 19:23:52

[code]SELECT State AS Sector,
COUNT(*) AS [Total Sync at NID]
FROM tblDATA_IDS1
WHERE JobType IN ('ibaddo', 'ibadps', 'ibadsp', 'ibadss', 'iradps', 'iradss'
AND WrStat IN ('assign', 'dispatch', 'pending'
AND State = 'GA'
AND CommitDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
AND CommitDateTime < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1)
GROUP BY State[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-19 : 19:24:54

do this.


    tblDATA_IDS1.CommitDateTime) >= Date()
AND tblDATA_IDS1.CommitDateTime) <  Date() + 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-19 : 19:26:40

I think David is using MS Access not MS SQL. Date(), cDate() is MS Access function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-19 : 19:29:42

That would explain why he is using double quotes instead of single quotes.
But why isn't he using proper MS ACCESS forum?

Peter Larsson
Helsingborg, Sweden

dkeener635
Starting Member

6 Posts

Posted - 2007-06-19 : 19:31:51

khtan. You caught me red-handed. I was writing the queries in access and then moving over to SQL since I am more familiar with microsoft access.
I am shamed...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-19 : 19:33:14

If you are going to use MSSQL, you better write in MS SQL's T-SQL syntax. Access syntax are diff from MS SQL.

dkeener635
Starting Member

6 Posts

Posted - 2007-06-19 : 19:37:03

thanks folks. Since it appears I have "slapped a hornet's nest here", I will move the question to MS ACCESS Forum. I am using both MS ACCESS and MSSQL at the present time but moving all to MSSQL, but it will take me some time.

David

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-19 : 21:35:00

If your query is intended for MSSQL then use Peter's query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources