Seperating Hours from Date Time

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

Seperating Hours from Date Time

Author

Topic

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2011-12-28 : 14:20:23

I've tried using Datepart and Dateadd but can't seem to pull out the hours section of the datetime field. After much searching, I've decided to just ask :)

Here's what I have so far:

DECLARE @Calendar1 AS DateTime
DECLARE @Calendar2 AS DateTime
SET @Calendar1 = '{{{ Please choose a start date. }}}'
SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SELECT convert(varchar, (dateadd(hour,-1,O.ReadyTimeFrom) ) ,8) AS 'Hour', O.ReadyTimeFrom,
O.OriginName, OT.Description AS 'Service Level', SUM(P.Price) AS Price

FROM tblOrder AS O
INNER JOIN tblOrderItems AS P ON O.OrderID = P.OrderID
INNER JOIN tblOrderTypes AS OT ON O.OrderType = OT.OrderTypeID

WHERE O.DueTimeTo BETWEEN @Calendar1 AND @Calendar2
AND O.Status <> 4

GROUP BY O.ReadyTimeFrom, O.OriginName, OT.Description

ORDER BY Hour

I am trying to get a list orders by hour, so the result would need to look something like:

0 12/23/2011 00:14:34 ABC Companies Rush $ 25.00
1 12/23/2011 01:25:00 XYZ Inc. Regular $ 15.00

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-28 : 14:42:58

I may not have completely understood your question, but would this work for you?

.....
SET @Calendar2 = '{{{ Please choose an end date. <(non inclusive)> }}}'

SELECT DATEPART(hour,O.ReadyTimeFrom) AS 'Hour', O.ReadyTimeFrom, 
O.OriginName, OT.Description AS 'Service Level', SUM(P.Price) AS Price
.....

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2011-12-29 : 00:37:03

Awesome. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources