Query to display day in sp

Question

In My project i have to display holiday lists of company

i want to create storeprocedure that return specific days of the period ,as parameter i am passing periods(from and to date) and day of the week ,and specific day.

example :

for the period 1-june-2013 to 30-june-2013 i need to get all sundays when i passed sunday as parameter

Jaganathan Bantheswaran · Answer

Hi Try this SP CREATE PROCEDURE getDaysBetweenTwoDate ( @FromDate DATETIME @ToDate DATETIME @dayOfWeek int -- should be 1 to 7 [sunday - satuday] ) AS BEGIN DECLARE @TOTALCount INT SET @FromDate = DATEADD(DAY-1@FromDate) Select @TOTALCount= DATEDIFF(DD@FromDate@ToDate); WITH d AS ( SELECT top (@TOTALCount) AllDays = DATEADD(DAY ROW_NUMBER() OVER (ORDER BY object_id) REPLACE(@FromDate'-''')) FROM sys.all_objects ) SELECT alldays datepart(dw alldays) From d where datepart(dw alldays) = @dayOfWeek RETURN END GO Exec getAllDaysBetweenTwoDate '06-01-2013''30-06-2014' 1

mohammed shamsheer · Answer

Thank u got every thing

mohammed shamsheer · Answer

ya got ....if it possible to retrive multiple days

mohammed shamsheer · Answer

Msg 8114 Level 16 State 5 Showing the error Procedure getDaysBetweenTwoDate Line 0 Error converting data type varchar to datetime.