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

Question

Given a datetime  is there a way we can know it happens to be a Saturday or Sunday   Any ideas and suggestions are appreciated

User · Answer

This expression  SELECT    DATEPART DW   my date var  - 1       DATEFIRST     7    will always return a number between 0 and 6 where  0 - gt  Sunday 1 - gt  Monday 2 - gt  Tuesday 3 - gt  Wednesday 4 - gt  Thursday 5 - gt  Friday 6 - gt  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

User · Answer

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   gt  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   gt  5  THEN  Weekend  ELSE  Weekday  END

User · Answer

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

User · Answer

This will get you the name of the day   SELECT DATENAME weekday  GETDATE

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server-2008] How to check if datetime happens to be Saturday or Sunday in SQL Server 2008

Examples related to sql-server-2008