How to check if datetime happens to be Saturday or

问题:

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

Any ideas and suggestions are appreciated!

回答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())

回答2:

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

回答3:

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

回答4:

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

回答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

回答6:

This will get you the name of the day:

SELECT DATENAME(weekday, GETDATE())

回答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