SQL Server- Check if Current Time is in Standard Work Week

Had to figure this out today, and thought I’d post it here, because I didn’t see it anywhere else out there.
There maybe better ways of doing this. If so, please let me know.

--Check to see if current time is a weekday and between 9 and 5
DECLARE @isWorkHours AS bit

SELECT @isWorkHours = COUNT(1)
WHERE
--Monday is 2 and Friday is 6.
DATEPART(dw,GETDATE()) BETWEEN 2 AND 6
AND
--This gets times between 9:00 and 4:59
DATEPART(hour, GETDATE()) BETWEEN 9 AND 16
IF @isWorkHours = 1
BEGIN
SELECT 1
END

IF @isWorkHours = 0
BEGIN
SELECT 0
END

2 Responses to SQL Server- Check if Current Time is in Standard Work Week

  1. Let’s try that again with properly escaped less than and greater than signs so they don’t get eaten by WordPress…


    DECLARE @date datetime
    SET @date = '10/7/2014 9:00 AM'

    IF DATEPART(dw,@date) >= 2 AND DATEPART(dw,@date) <= 6 AND DATEPART(hour, @date) >= 9 AND DATEPART(hour, @date) <= 16
    SELECT 1
    ELSE
    SELECT 0

    • Thanks Adam.
      It’s probably obvious, but in case it helps someone, one little change to make the date dynamic:

      DECLARE @date datetime
      SET @date = GETDATE()

      IF DATEPART(dw,@date) >= 2 AND DATEPART(dw,@date) < = 6 AND DATEPART(hour, @date) >= 9 AND DATEPART(hour, @date) < = 16 SELECT 1 ELSE SELECT 0

Leave a Reply to Adam Cancel reply

Your email address will not be published. Required fields are marked *