Help With Datepart - 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 Datepart

Author

Topic

jerrypenna
Starting Member

1 Post

Posted - 2008-05-31 : 09:01:13

Hi All,

i'm trying to format SQL so that I retrive the day of the week and the hr in the same column.

SELECT Datepart([hour], Time) as Hour, SUM(Total) as Sales, count(TransactionNumber) as Customers, SUM(Total)/count(TransactionNumber) as 'Ave Sale'
FROM [transaction]
WHERE time between '05/30/2008' and '05/31/08'
GROUP BY datepart([hour],Time)

Output desired is

05/30/08 09
05/30/08 10
05/30/08 11
...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-31 : 09:37:09

This will GROUP BY and SELECT a datetime value "rounded" to the hour. You should use your frontend application to display the value as you like:


SELECT	dateadd(hour, datediff(hour, 0, [time]), 0) as Hour, 
	SUM(Total) as Sales, 
	count(TransactionNumber) as Customers, 
	SUM(Total)/count(TransactionNumber) as 'Ave Sale'
FROM [transaction] 
WHERE time between '05/30/2008' and '05/31/08'
GROUP BY datediff(hour, 0, [time])

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources