i am writing a stored procedure to retrive the days in the particular month...its shows an error like "incorrect syntax near ')'"
this is my procedure..pls help me
CREATE PROCEDURE datecheck
@EmpId int,@fdate datetime,@tdate datetime
as
begin
set nocount on
--here get fdate and tdate of that empid and other thing are same which you are using
Select @fdate = fdate ,@tdate =tdate from finaldatecheck where EmpId = @EmpId
SELECT TOP (DATEDIFF(MONTH, @fdate, @tdate)+1) ROW_NUMBER() OVER
(ORDER BY [object_id])-1 FROM sys.all_objects
),
Mon(n,fd,ld) AS
(
SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
FROM n, (SELECT DATEADD(DAY, 1-DAY(@fdate), @fdate)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld)
- CASE WHEN @fdate > fd THEN (DATEDIFF(DAY, fd, @fdate)+1) ELSE 0 END
- CASE WHEN @fdate < ld THEN (DATEDIFF(DAY, @tdate, ld)-1) ELSE 0 END
FROM mon
end
