Loading

Stored Procedure

sunakshi saxena

14y1.6k

I need a sql server stored procedure like i will pass start date and end date for leave and it will return no of days between start date and end date.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Hi,

Hopefully this will help you to get.

CREATE PROCEDURE Proc_NumberofLeaves
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
SET NOCOUNT ON
SELECT DATEDIFF(DD,@StartDate,@EndDate) AS [NoOfLeaves]
END

Output:

DECLARE @StDate DATETIME
DECLARE @EdDate DATETIME
SET @StDate = GETDATE()-25
SET @EdDate = GETDATE()

EXEC Proc_NumberofLeaves @StDate,@EdDate
Gohil Jayendrasinh14y ago
HI

DECLARE @DateOld DATETIME,
@DateNew datetime SET @DateOld = '10-Jan-2012'

SET @DateNew = GETDATE()
SET DATEFIRST 1
SELECT DATEDIFF(DAY, @DateOld, @DateNew) -(2 * DATEDIFF(week, @DateOld, @DateNew))
- CASE
WHEN DATEPART(weekday, @DateOld + @@DATEFIRST) = 1 THEN 1
ELSE 0
END - CASE
WHEN DATEPART(weekday, @DateNew + @@DATEFIRST) = 1 THEN 1
ELSE 0
END