Select statement help?

Question

I have a field in my database with the following data in it:
"12/1/2004 10:02:14 AM"
They are the time and date the sales transaction took place. I would like to SUM all of the sales that took place during the 10 AM hour, 11 AM hour, 12 pm hour, and so on. How do I select out only the hour characters and the am/pm characters?

Using SQL code

Answer 1

You can use the DATEPART() function if you're only concerned with the hour.

Note that this query assumes you don't care about the date, just the hour of the day. It will sum all of the sales that took place in the 10 o'clock hour, regardless of the date. You weren't really specific as to whether or not the date mattered. It would be easy to modify to take this into account.

SELECT
COUNT(SalesID) AS NumberOfSales,
DATEPART(hh, TransactionDate) AS HourOfDay

FROM

Sales

GROUP BY DATEPART(hh, TransactionDate)

ORDER BY DATEPART(hh, TransactionDate)


Note that this will use military time to handle AM/PM.

Answer 2

without myself looking into it deeply a CASE statement in the SQL query might do the trick but I am not sure

Sum(case ....end case)

Answer 3

Tedious, but:
Hour:

10
...

AM or PM

AM
PM