Need help with SQL 2000 server

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)

Need help with SQL 2000 server

Author

Topic

waynelambright
Starting Member

3 Posts

Posted - 2006-06-03 : 15:08:09

Need help with SQL count,I'm using sql server 2000.

I’m basically trying to count records based on a date, I would like to show a count of records for that day, and then the day after, but the long date format{3/12/2006 9:50:49 PM}, 1/300th of a second is creating errors and it will not count the records for that day,

3/12/2006 9:50:49 PM
3/12/2006 9:50:48 PM
3/12/2006 9:50:47 PM
3/12/2006 9:50:46 PM
3/12/2006 9:50:45 PM

Should show a count of 5, but since they're all 1 second off, it shows individual counts of one.

str_date is the date column

SELECT TOP 100 PERCENT COUNT(str_date) AS Expr1, str_date
FROM dbo.rating
GROUP BY str_date
ORDER BY str_date DESC

thanks

wayne
http://sfSurvey.com

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-03 : 15:12:43

SELECT COUNT(*) AS Expr1, convert(varchar(8),str_date,112)
FROM dbo.rating
GROUP BY convert(varchar(8),str_date,112)
ORDER BY convert(varchar(8),str_date,112) DESC

Doesn't affect this (but your later question probably will) but are you v2000 or v2005 - the title says 2005 and the question 2000.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

waynelambright
Starting Member

3 Posts

Posted - 2006-06-03 : 16:50:33

Thanks nr

I have changed the title. I'm using sql server 2000. I'll give this a try and let you know.

thanks for your help.

Wayne

waynelambright
Starting Member

3 Posts

Posted - 2006-06-03 : 17:02:25

Hi nr,

it worked, thanks so much.

if you have the time, why does this work? the varchar 8 does that put it into a string or someting?

thanks again for all your help.

Wayne

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-03 : 17:16:28

You can also do this by converting the datetime column to midnight on that date. That is usually the most efficient way, instead of converting to a string, because the dateadd/datediff functions are much faster than the conversions to strings.

The same applies to weeks, months, quarters, years, hours, minutes, etc. These links show how to do these conversions, and have functions you can use directly.
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

There are other SQL Server datetime manipulations here:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762


select 
	[Date] = dateadd(dd,datediff(dd,0,str_date),0),
	[Count] = count(*)
from
	dbo.rating
group by
	dateadd(dd,datediff(dd,0,str_date),0)
order by
	dateadd(dd,datediff(dd,0,str_date),0) desc

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources