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

Given a datetime, is there a way we can know it happens to be a Saturday or Sunday.

Any ideas and suggestions are appreciated!

sql-server-2008

Solution 1:^[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:^[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:^[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:^[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:^[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

Solution 6:^[6]

This will get you the name of the day:

SELECT DATENAME(weekday, GETDATE())

Solution 7:^[7]

ok i figure out :

DECLARE @dayName VARCHAR(9), @weekenda VARCHAR(9), @free INT
SET @weekenda =DATENAME(dw,GETDATE())

IF (@weekenda='Saturday' OR @weekenda='Sunday')
SET @free=1
ELSE
SET @free=0

than i use : .......... OR free=1

Solution 8:^[8]

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

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	msmucker0527
Solution 2	Thanasis Ioannidis
Solution 3	Chirag
Solution 4	JonH
Solution 5	Peter Albert
Solution 6	Arion
Solution 7	Oliver
Solution 8	manoj rajupeta

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

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]