How to change month and year to FromDate and ToDate in SP

[yeongsm]

Dear all,
Can somebody help me to revise below original SP where it would display cardno for the month period. What I want is to display cardno by date range Eg, txtLog_date and txtCompleted_date

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



-- Batch submitted through debugger: db-deploy.sql|6|0|C:\CorpServer\ATTD\Report\db-deploy.sql

Alter PROC [dbo].[$_Rpt_vehicle]
@Card VARCHAR(16), -- Employee Card No.
@Year INT, -- Trx. Year
@Mnth INT, -- Trx. Month

@txtLog_date datetime, ------ FromDate newly added field passed from asp
@txtCompleted_date datetime ------------ ToDate newly added field passed from asp


AS SET NOCOUNT ON
BEGIN
DECLARE @Temp TABLE ( [Id] INT IDENTITY(1,1), [Date] DATETIME, [WDay] NVARCHAR(100) )
DECLARE @DtFr DATETIME, @DtTo DATETIME

SET @DtFr = CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME)
SET @DtTo = DATEADD(month, 1,CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME))

;WITH tData AS
( SELECT @DtFr [Date] UNION ALL SELECT DATEADD(DAY,1,[Date]) FROM tData WHERE DATEADD(DAY,1,[Date]) < @DtTo )
INSERT INTO @Temp ( [Date], [WDay] ) SELECT mt.[Date], DATENAME(WEEKDAY,mt.[Date]) FROM tData mt

SELECT
'',

CardNo [EmplCard], Name [EmplName], [DepartmentDesc] [EmplSrcs], [InOut] [InOutDes],

TrDate [TrnxDate], TrTime [TrnxTmMn], TrController [TrnxTmMx],

''

FROM VW_RPT_van2

WHERE
CardNo = @Card AND YEAR(TrDate) = @Year AND MONTH(TrDate) = @Mnth
GROUP BY
TrDate, [TrDay], TrTime, CardNo, Name, [DepartmentDesc], [InOut] , TrController
END
------------------- end of SP ---------------------------------

 

[TamarGranor]

If I understand your question, you don't need these lines:

SET @DtFr = CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME)
SET @DtTo = DATEADD(month, 1,CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME))

;WITH tData AS
( SELECT @DtFr [Date] UNION ALL SELECT DATEADD(DAY,1,[Date]) FROM tData WHERE DATEADD(DAY,1,[Date]) < @DtTo )
INSERT INTO @Temp ( [Date], [WDay] ) SELECT mt.[Date], DATENAME(WEEKDAY,mt.[Date]) FROM tData mt

Just change the WHERE clause of your query to:

WHERE
CardNo = @Card AND TrDate BETWEEN @txtLog_date and @txtCompleted_date

The one minor complication is that you talked about dates, but you're using datetimes. Is the time portion relevant? If not, you need to clean it up so that your start date has a time of 00:00 and your end date has a time of 23:59.

Tamar