SQL Code - Is Weekend - user defined function

SQL Code - Is Weekend - user defined function

I need to know - "Is this day a weekend?" This function is used to determine if a date is a Saturday or Sunday then 1 else 0.

/****** Object:  UserDefinedFunction [dbo].[SA_weekend]    Script Date: 05/23/2012 11:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SA_weekend]
(
 @Date datetime
)
RETURNS int
AS
 /********************************************************************************************
 Description:  This function is used to determine if a date is a weekend then 1 else 0. (Saturday or Sunday)

 dbo.[SA_weekend]
 ********************************************************************************************/
 Begin
 Declare @IsWeekend int
 Select @IsWeekend = 0

 Declare @DayofWeek varchar(10)
 --SET DATEFIRST 7;
 SET @DayofWeek = (SELECT
        case when DATEPART(dw, @Date) = 1 then 'Sunday'
        when DATEPART(dw, @Date) = 2 then 'Monday'
        when DATEPART(dw, @Date) = 3 then 'Tuesday'
        when DATEPART(dw, @Date) = 4 then 'Wednesday'
        when DATEPART(dw, @Date) = 5 then 'Thursday'
        when DATEPART(dw, @Date) = 6 then 'Friday'
        when DATEPART(dw, @Date) = 7 then 'Saturday'
        end)

 If @DayofWeek = 'Sunday'
 begin
  Set @IsWeekend = 1
 end
 else if @DayofWeek = 'Saturday'
 begin
  Set @IsWeekend = 1
 end

 Return @IsWeekend
  End

GO

To test this script -

--testing dates for weekends
declare @StartDate datetime
--set @StartDate = '5/25/2012'
--set @StartDate = '5/26/2012'
--set @StartDate = '5/27/2012'
set @StartDate = '5/27/2011'
--set @StartDate = '5/28/2011'
--set @StartDate = '5/29/2011'

select dbo.SA_weekend(@StartDate)

SQL Holiday Dates user defined function

SQL Code Description:  This user defined function is used to determine if a date is a holiday then 1 else 0.

/****** Object:  UserDefinedFunction [dbo].[SA_holidayDates]    Script Date: 05/23/2012 10:24:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SA_holidayDates]
(
 @Date datetime
)
RETURNS int
AS
 /********************************************************************************************
 Description:  This function is used to determine if a date is a holiday then 1 else 0.
 Holidays are Christmas, New Year's Day, July 4th, Memorial Day (last Monday of May) and Labor Day (first Monday of September)

 dbo.SA_holidayDates
 ********************************************************************************************/
  begin
 Declare @IsHoliday int
 Select @IsHoliday = 0
 if @Date = (select CAST('12/25/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))
  or @Date = (select CAST('1/1/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))
  or @Date  = (select CAST('7/4/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))
  or @Date = (select DATEADD(wk, DATEDIFF(wk,0,CAST('5/31/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE)), 0))
  or @Date = (select DATEADD(wk, DATEDIFF(wk,0,CAST('9/1/' + convert(varchar(4),(datepart(yyyy,@Date))) as DATE))+1, 0) )
 
  Set @IsHoliday = 1
 
--select @IsHoliday
 Return @IsHoliday
  End

GO

To test your data - use this testing query -
--testing dates for holidays
declare @StartDate datetime
--set @StartDate = '5/28/2012'
--set @StartDate = '9/3/2012'
--set @StartDate = '1/1/2012'
--set @StartDate = '12/25/2012'
--set @StartDate = '7/4/2012'
set @StartDate = '5/30/2011'
--set @StartDate = '9/5/2011'
--set @StartDate = '1/1/2011'
--set @StartDate = '12/25/2011'
--set @StartDate = '7/4/2011'

select dbo.SA_holidayDates(@StartDate)

--compliments of Ann Roth