How to check if datetime happens to be Saturday or Sunday in SQL Server 2008

sql-server-2008

Solution 1:

Many ways to do this, you can use DATENAME and check for the actual strings 'Saturday' or 'Sunday'

SELECT DATENAME(DW, GETDATE())

Or use the day of the week and check for 1 (Sunday) or 7 (Saturday)

SELECT DATEPART(DW, GETDATE())

Solution 2:

This expression

SELECT (((DATEPART(DW, @my_date_var) - 1 ) + @@DATEFIRST ) % 7)

will always return a number between 0 and 6 where

0 -> Sunday
1 -> Monday
2 -> Tuesday
3 -> Wednesday
4 -> Thursday
5 -> Friday
6 -> Saturday

Independently from @@DATEFIRST

So a weekend day is tested like this

SELECT (CASE
           WHEN (((DATEPART(DW, @my_date_var) - 1 ) + @@DATEFIRST ) % 7) IN (0,6)
           THEN 1
           ELSE 0
       END) AS is_weekend_day

Solution 3:

DECLARE @dayNumber INT;
SET @dayNumber = DATEPART(DW, GETDATE());

--Sunday = 1, Saturday = 7.
IF(@dayNumber = 1 OR @dayNumber = 7) 
    PRINT 'Weekend';
ELSE
    PRINT 'NOT Weekend';

This may generate wrong results, because the number produced by the weekday datepart depends on the value set by SET DATEFIRST. This sets the first day of the week. So another way is:

DECLARE @dayName VARCHAR(9);
SET @dayName = DATEName(DW, GETDATE());

IF(@dayName = 'Saturday' OR @dayName = 'Sunday') 
    PRINT 'Weekend';
ELSE
    PRINT 'NOT Weekend';

Solution 4:

SELECT DATENAME(weekday, GetDate())

Check this for sql server: http://msdn.microsoft.com/en-US/library/ms174395(v=sql.90).aspx Check this for .net: http://msdn.microsoft.com/en-us/library/bb762911.aspx

Solution 5:

Attention: The other answers only work on SQL Servers with English configuration! Use SET DATEFIRST 7 to ensure DATEPART(DW, ...) returns 1 for Sunday and 7 for Saturday.

Here's a version that is independent of the local setting and does not require to use :

CREATE FUNCTION [dbo].[fct_IsDateWeekend] ( @date DATETIME )
RETURNS BIT
AS
BEGIN
    RETURN CASE WHEN DATEPART(DW, @date + @@DATEFIRST - 1) > 5  THEN 1 ELSE 0 END;
END;

If you don't want to use the function, simply use this in your SELECT statement:

CASE WHEN DATEPART(DW, YourDateTime + @@DATEFIRST - 1) > 5  THEN 'Weekend' ELSE 'Weekday' END