Here the sample code.

Please help me out in this.

If I give the dates directly The stored procedure is giving the result is good.

If I give the dates with the variables ,I am not getting the result.

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

--=============================================================================================

ALTER PROCEDURE dbo.usp_RptSales

@ParmAsOfDatedatetime,

@ParmVersionchar(6)

AS

SET NOCOUNT ON

DECLARE @AsOfBusinessDaysmallint

DECLARE @AsOfBeginDatedatetime

DECLARE @AsOfEndDatedatetime

DECLARE @AsOfEndDatePriordatetime

DECLARE @MonthBeginDatedatetime

DECLARE @MonthBeginDatePriordatetime

DECLARE @MonthEndDatedatetime

DECLARE @HealthProductCodevarchar(6)

SET @AsOfEndDate = dbo.uf_RptGetDayEndDate(@ParmAsOfDate)

SET @MonthBeginDate = dbo.uf_RptGetMonthBeginDate(@ParmAsOfDate)

SET @MonthEndDate = dbo.uf_RptGetMonthEndDate(@ParmAsOfDate)

SET @AsOfBusinessDay = dbo.uf_RptGetBusinessDay(@ParmAsOfDate, 'M')

SET @AsOfEndDatePrior = dbo.uf_RptGetDayEndDate(dbo.uf_RptGetBusinessDate(DATEADD(Month, -1, @MonthBeginDate) , @AsOfBusinessDay, 'M'))

IF @AsOfEndDatePrior IS NULL AND @AsOfBusinessDay > 0

BEGIN

SET @AsOfEndDatePrior = dbo.uf_RptGetMonthEndDate(DATEADD(Month, -1, @MonthBeginDate))

END

IF @AsOfEndDatePrior IS NULL AND @AsOfBusinessDay = 0

BEGIN

SET @AsOfEndDatePrior = dbo.uf_RptGetMonthBeginDate(DATEADD(Month, -1, @AsOfBeginDate))

END

SET @MonthBeginDatePrior = dbo.uf_RptGetMonthBeginDate(@AsOfEndDatePrior)

SELECTdistinct RptPolicy.ApplicationID,

xxxx.ApplicantState,

xxxx.dtTransactionCompletion,

xxxx.dtSubmitDate,

xxxx.PolicyStatus,

ProductGroupCode = convert(varchar(12), ProductGroupCode),

xxxx.DistributionChannelCode,

ISNULL(AORChannels.SubDCC,'A') as SubDCC,

--AORChannels.SubDccName,

SubmitDay= (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN @AsOfBeginDate AND @AsOfEndDate),

--SubmitDay= (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN 'Aug 17 2007 12:00AM' AND 'Aug 17 2007 11:59PM'),

IssueDay= (SELECT 1 WHERE xxxx.PolicyStatus = 'A' AND dtTransactionCompletion BETWEEN @AsOfBeginDate AND @AsOfEndDate),

SubmitMonth= (SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN @MonthBeginDate AND @AsOfEndDate),

IssueMonth= (SELECT 1 WHERE xxxx.PolicyStatus = 'A' AND dtTransactionCompletion BETWEEN @MonthBeginDate AND @AsOfEndDate),

SubmitMonthPrior=

(SELECT 1 WHERE xxxx.dtSubmitDate BETWEEN @MonthBeginDatePrior AND @AsOfEndDatePrior),

IssueMonthPrior=

(SELECT 1 WHERE xxxx.PolicyStatus = 'A' AND xxxx.dtTransactionCompletion BETWEEN @MonthBeginDatePrior AND @AsOfEndDatePrior),

ApplicantCount = dbo.uf_RptGetApplicantCount(RptPolicy.ApplicationID, 'Y'),

ParmAsOfDate = @ParmAsOfDate,

AsOfBeginDate = @AsOfBeginDate,

AsOfEndDate = @AsOfEndDate,

MonthBeginDate = @MonthBeginDate,

AsOfBusinessDay = @AsOfBusinessDay,

AsOfEndDatePrior = @AsOfEndDatePrior,

MonthBeginDatePrior = @MonthBeginDatePrior,

HealthProductCode,

IWPCode,

IWPApplicantCount = dbo.uf_RptGetIWPApplicantCount(RptPolicy.ApplicationID)

--------

INTOdbo.#RptTemp

FROMxxxx

left outer join dbo.xxxx on xxxx.AOR1=xxxx.AOR

left outer join xxxx prod on xxxx.applicationid=xxxx.applicationid

Where xxxx.dtSubmitDate BETWEEN 'Aug 17 2007 12:00AM'

AND 'Aug 17 2007 11:59AM' and PolicyStatus = 'A'