CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Marko Lombardi
Marko Lombardi

Reputation: 131

SQL dynamic date but fixed time query

I am trying to write a sql query like the example below, however, I need it to always choose the DateEntered field between the current day's date at 8:00am and the current day's date at 4:00pm. Not sure how to go about this. Can someone please help?

SELECT     OrderNumber
    , OrderRelease
    , HeatNumber
    , HeatSuffix
    , Operation
    , COUNT(Operation) AS [Pieces Out of Tolerance]
FROM         Alerts
WHERE     (Mill = 3) 
    AND (DateEntered BETWEEN GetDate '08:00' AND GetDate '16:00')
GROUP BY OrderNumber, OrderRelease, HeatNumber, HeatSuffix, Operation

Upvotes: 2

Views: 6774

Answers (5)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This could work for you 

 DateEntered BETWEEN dateadd(hour, 8, cast(cast(getdate() as date) as datetime)) AND 
                     dateadd(hour, 16, cast(cast(getdate() as date) as datetime))

But that would (as your query does) include rows where the time is 16:00:00.000' but not rows with time 16:00:00.003'.

If you meant to include all rows before 17:00 use this instead.

 DateEntered >= dateadd(hour, 8, cast(cast(getdate() as date) as datetime)) AND
 DateEntered <  dateadd(hour, 17, cast(cast(getdate() as date) as datetime))

Upvotes: 0

Gonzalo.-
Gonzalo.-

Reputation: 12672

SELECT     OrderNumber
    , OrderRelease
    , HeatNumber
    , HeatSuffix
    , Operation
    , COUNT(Operation) AS [Pieces Out of Tolerance]
FROM         Alerts
WHERE     (Mill = 3) 
    AND (datediff(dd,DateEntered,getdate()) = 0 AND Datepart(hh,DateEntered) BETWEEN '08' AND '16')
GROUP BY OrderNumber, OrderRelease, HeatNumber, HeatSuffix, Operation

Upvotes: 4

EkoostikMartin
EkoostikMartin

Reputation: 6911

SELECT     OrderNumber
    , OrderRelease
    , HeatNumber
    , HeatSuffix
    , Operation
    , COUNT(Operation) AS [Pieces Out of Tolerance]
FROM         Alerts
WHERE     (Mill = 3) 
    AND (DateEntered BETWEEN DATEADD(hour, 8, CONVERT(datetime,CONVERT(date,getdate()))) AND DATEADD(hour, 18, CONVERT(datetime,CONVERT(date,getdate()))))
GROUP BY OrderNumber, OrderRelease, HeatNumber, HeatSuffix, Operation

Upvotes: 0

Taryn
Taryn

Reputation: 247680

Since you are using SQL Server 2008 you can CAST as either date or time. To get the current date at 08:00 you will use:

(CAST(CAST(getdate() AS DATE) AS DATETIME) + CAST('08:00' AS TIME))

To get the current date at 16:00:

(CAST(CAST(getdate() AS DATE) AS DATETIME) + CAST('16:00' AS TIME)))

Putting this into your query:

SELECT OrderNumber
    , OrderRelease
    , HeatNumber
    , HeatSuffix
    , Operation
    , COUNT(Operation) AS [Pieces Out of Tolerance]
FROM         Alerts
WHERE     (Mill = 3) 
    AND (DateEntered 
        BETWEEN (CAST(CAST(getdate() AS DATE) AS DATETIME) + CAST('08:00' AS TIME)) 
        AND (CAST(CAST(getdate() AS DATE) AS DATETIME) + CAST('16:00' AS TIME)))
GROUP BY OrderNumber, OrderRelease, HeatNumber, HeatSuffix, Operation

Upvotes: 0

Christian Hayter
Christian Hayter

Reputation: 31071

DECLARE @TodayODBC varchar(30), @DateFrom datetime, @DateTo datetime
SET @TodayODBC = CONVERT(varchar(10), GETDATE(), 120)
SET @DateFrom = CONVERT(datetime, @TodayODBC + ' 08:00:00', 120)
SET @DateTo   = CONVERT(datetime, @TodayODBC + ' 16:00:00', 120)

SELECT OrderNumber, OrderRelease, HeatNumber, HeatSuffix, Operation,
    COUNT(Operation) AS [Pieces Out of Tolerance]
FROM Alerts
WHERE (Mill = 3) 
AND (DateEntered BETWEEN @DateFrom AND @DateTo)
GROUP BY OrderNumber, OrderRelease, HeatNumber, HeatSuffix, Operation

The , 120 specifies ODBC datetime format, which is constant regardless of the locale.

Upvotes: 3

Related Questions