[sql-server-2008] 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!

This will get you the name of the day:

SELECT DATENAME(weekday, GETDATE())

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

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

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

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';

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