Group by Datepart(hh,datetimestamp) problem 1

[Glowworm27]

Hello all,

I have a hit counter that records when a page is hit. I only care about the hour that the page was hit. So I am trying to create a report that will display the time in 12 hour Am/Pm format. But the Datepart() function returns a number from 0 to 23 I want to convert that in the select statement using a Case, but get an error about select not being in the agregate function..

Select Count(*) as HitCounter
, Case when Datepart(hh,DateTimeStamp) < 13 then (Convert(varchar(2), Datepart(hh,DateTimeStamp)) + 'AM') 
Else (Convert(varchar(2), (Datepart(hh,DateTimeStamp) - 12)) + 'PM')
End as HitHour
From tblHits
Where CounterName = 'SearchInvoice'
Group By Datepart(hh,DateTimeStamp)

if there is a way to get the data in 12 hour format that would be great thanks.



George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[RiverGuy]

I don't see where that query would cause an error. Works fine for me.

 

[Glowworm27]

here is the error i am getting

Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblHits.Datetimestamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblHits.Datetimestamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[RiverGuy]

Which version of SQL?

 

[Glowworm27]

sql 2000 sorry

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[RiverGuy]

I don't have SQL 2000 installed locally, but test this query and let me know if it works:

DECLARE @tblHits TABLE (DateTimeStamp DATETIME, CounterName VARCHAR(50))

INSERT INTO @tblHits SELECT GETDATE(), 'SearchInvoice'
INSERT INTO @tblHits SELECT GETDATE(), 'SearchInvoice'
INSERT INTO @tblHits SELECT '2008-10-27T09:15:00.000', 'SearchInvoice'


Select Count(*) as HitCounter
, 
Case 
	when Datepart(hh,DateTimeStamp) < 13 then (Convert(varchar(2), Datepart(hh,DateTimeStamp)) + 'AM')
	Else (Convert(varchar(2), (Datepart(hh,DateTimeStamp) - 12)) + 'PM')
End as HitHour
From @tblHits
Where CounterName = 'SearchInvoice'
GROUP BY 
Case 
	when Datepart(hh,DateTimeStamp) < 13 then (Convert(varchar(2), Datepart(hh,DateTimeStamp)) + 'AM')
	Else (Convert(varchar(2), (Datepart(hh,DateTimeStamp) - 12)) + 'PM')
End

 

[Glowworm27]

thanks riverguy, didnt know I had to carry down the case statement into the group by clause, but it does make sence now.

All works fine thanks

G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net