Amol Patil

DECLARE @my int DECLARE @myDeduct int DECLARE @day INT DECLARE @mydate DATETIME SET @mydate = getdate() SET @myDeduct = 0 SET DateFirst 1 -- Set it monday=1 (value) --Saturday and Sunday on the first and last day of a month will Deduct 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5) SET @myDeduct = @myDeduct + 1 IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5) SET @myDeduct = @myDeduct + 1 SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate))) select (((@my/7) * 5 + (@my%7)) - @myDeduct) as Working_Day_for_month Output:- 23 days for ( August 2011)

Step 1:- Create SQL Function /****** Object: UserDefinedFunction [dbo].[getTotalDaysInMonth] ******/ SET ANSI_ NULL S ON GO SET QUOTED_IDENTIFIER ON GO   -- ============================================= -- Author: -- Create date: <25th July, 2009> -- Description: -- ============================================= CREATE FUNCTION [ dbo ] . [ getTotalDaysInMonth ] ( -- Add the parameters for the function here @anydateofMonth DATETIME ) RETURNS INT AS BEGIN -- Declare the return variable here DECLARE @totalDaysInMonth INT -- Add the T-SQL statements to compute the return value here   DECLARE @givendate DATETIME SET @givendate = @anydateofMonth   SET @givendate = STR ( YEAR ( @givendate ) ) + '-' + STR ( MONTH ( @givendate ) + 1 ) + '-01'   SELECT @totalDaysInMonth = DATEPART ( dd, DATEADD ( DAY , - 1 , @givendate ) )   -- Return the result of the f...

CREATE PROCEDURE dbo.SearchClients ( @ClientFirstName varchar ( 20 ) = null , @ClientLastName varchar ( 20 ) = null , @ClientHasTattoo bit = null , @JobFinished bit = null , @JobTypeID smallint = null , @ArtistID Smallint = null , @JobTotalHoursMinimum float = null , @JobTotalHoursMaximum float = null , @AdvertisingID smallint = null , @ClientDOBMinimum datetime = null , @ClientDOBMaximum datetime = null , @ClientStreetAddress varchar ( 75 ) = null , @ClientCity varchar ( 20 ) = null , @ClientState varchar ( 2 ) = null , @ClientZip varchar ( 10 ) = null , @ClientHomePhone varchar ( 14 ) = null , @ClientWorkPhone varchar ( 14 ) = null , @ClientEmail varchar ( 50 ) = null , @ClientSex bit = null , @JobDateMinimum datetime = null , @JobDateMaximum datetime = null , @JobTitle varchar ( 50 ) = null ) AS IF @JobFinished IS NULL AND @JobTypeID IS NULL AND @ArtistID IS NULL AND @JobTotalHoursMaximum IS NULL A...

DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) , 'Last Day of Previous Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) , 'Last Day of Current Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) , 'First Day of Next Month'

DECLARE @EndOfPrevWeek DateTime DECLARE @StartOfPrevWeek DateTime --get number of a current day (1-Monday, 2-Tuesday... 7-Sunday) SET @TodayDayOfWeek = datepart(dw, GetDate()) print @TodayDayOfWeek --get the last day of the previous week (last Sunday) SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate()) print @EndOfPrevWeek --get the first day of the previous week (the Monday before last) SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate()) print @StartOfPrevWeek --get the last day of the previous week (last Sunday) print DATEADD(dd, 7-@TodayDayOfWeek, GetDate()) --get the first day of the previous week (the Monday before last) print DATEADD(dd, -(@TodayDayOfWeek-1), GetDate())