SQL Server Performance Forum – Threads Archive

Excluding Non-Business Hours

I want to create a statement that excludes weekends and only includes business hours, 9:30 AM – 9:30 PM. I have highlighted the section in red that I cannot figure out. Please help. =============================================================
SELECT
LU_SERVERNAME.SERVERNAME, SDB_METRICS.METRICUPDATETIME, SDB_METRICS.MEANMETRICVALUE FROM
LU_METRIC, LU_METRICCOUNTER, LU_OBJECT, LU_INSTANCE, SDB_METRICS, LU_SERVER, LU_SERVERNAME WHERE
DATABASE1.dbo.LU_METRIC.FK_METRICCOUNTERID = DATABASE1.dbo.LU_METRICCOUNTER.PK_METRICCOUNTERID AND
DATABASE1.dbo.LU_METRIC.FK_OBJECTID = DATABASE1.dbo.LU_OBJECT.PK_OBJECTID AND
DATABASE1.dbo.LU_METRIC.FK_INSTANCEID = DATABASE1.dbo.LU_INSTANCE.PK_INSTANCEID AND
DATABASE1.dbo.LU_METRIC.PK_METRICID = DATABASE1.dbo.SDB_METRICS.FK_METRICID AND
DATABASE1.dbo.SDB_METRICS.FK_SERVERID = DATABASE1.dbo.LU_SERVER.PK_SERVERID AND
DATABASE1.dbo.LU_SERVER.FK_SERVERNAMEID = DATABASE1.dbo.LU_SERVERNAME.PK_SERVERNAMEID AND
LU_SERVERNAME.SERVERNAME = ‘SERVER001’ AND
LU_METRIC.FK_METRICCOUNTERID = ‘9’AND
LU_METRIC.FK_OBJECTID = ‘7’ AND
LU_METRIC.FK_INSTANCEID = ‘4’ AND
NOT (DATEPART(hh, METRICUPDATETIME) >= 09 + (mm, METRICUPDATETIME) >= 30) AND DATEPART(hh, METRICUPDATETIME) <= 21) + (mm, METRICUPDATETIME) >= 30) ANDNOT (DATEPART(dw, METRICUPDATETIME) = 1 OR DATEPART(dw, METRICUPDATETIME) = 7))

One way to do it is.. <pre> WHERE CONVERT(datetime, ‘2004-01-01 ‘ + CONVERT(varchar, METRICUPDATETIME, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) >= ‘2004-01-01 09:30:00’ and CONVERT(datetime, ‘2004-01-01 ‘ + CONVERT(varchar, METRICUPDATETIME, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) < ‘2004-01-01 21:30:00’ </pre> Not the most elegant way perhaps, but nice and readable at least

Chappy…thanks. I will give this a try.

]]>