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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-18 : 09:49:35
|
| Oliver writes "I am trying to retrieve stats relating to number of records in a hour. So each record has a time stamp entry and an amount in dolalrs. In MS Excel you can calcualate the sum and count of amount for each hour of the day to give you peaks and troughs throughout the day (i.e field A contains timestamp, field B contains amount, if you creat a column C=INT(A) you get the Day without the time, then you perform the follwing calcualtion in column d=INT((A-C)*24) you get an hour of the day for each record, between 0 and 23 where 23 is equivalent to 11pm and 0 is 12am) you can then count and sum column B grouping by A and return number of transactions and amount for each hour of the day.After that rather lengthy explanation I want to be able to do the same in SQL. So:select sum(amount), count(amount), INT((INT(timestamp)-timestamp))*24 as tod, from recordset group by TODUnfortunately INT is not a recognised SQL command and the timestamp appears to be a text field. I know I haven't give you much to work with but if you catch my drift on this one a possible solution would be wonderful.Cheers,Oliver" |
|
|
dsdeming
479 Posts |
Posted - 2002-04-18 : 09:59:56
|
| First of all, a timestamp is a varbinary datatype; it has nothing to do with date or time. Use a datetime or smalldatetime datatype instead. As for your INT() function, see CAST or CONVERT in BOL. |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-04-18 : 10:26:41
|
Hmm, given the choice between DATEPART(hour, dtvalue) and CAST(ROUND((CAST(dtvalue AS float) - FLOOR(CAST(dtvalue AS float))) * 24, 6) AS INT), I know which I'd choose.  |
|
|
|
|
|
|
|
|
Date Integer