In Cr 2008 I have main report (just container for sub reports) and two sub reports that are based on same command.
I am passing Run_Date parameter from main report to those two sub reports.
In each sub I created Run_Date parameter same name as in main report.
Report run but no showing any records empty.
I assume that problem is with my date filed in command:
Part of SQL command script:
Declare @Run_date Date
Declare @Edge_date Date
Set @Run_date ={?Run_Date}
Set @Edge_Date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
-- Range 1:
-- Range1 is range from Run_Date parameter back to previous YTD beging January 01. To Current Parameter full month date e.g. Sep.26.2018
-- Range will be from 2017-01-01 to 2018-09-31.
--Range 2:
-- Range 2 is range from laset YTD relating to Current Run_Date Parameter full month e.g. if Run_Date parameter is Sep.26.2018
-- Range2 will be from 2017-01-01 to 2017-09-31.
Declare @Start_date_Range1 Date
Set @Start_date_Range1 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)-1, 0) -- beginning of previous year
Declare @End_date_Range1 Date
Set @End_date_Range1 = DateAdd(mm, DateDiff(mm, 0, @Run_date) +1, 0) -- End of Run_Date month this year
Declare @Start_date_Range2 Date
Set @Start_date_Range2 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)-1, 0) -- beginning of previous year
Declare @End_date_Range2 Date
Set @End_date_Range2 = DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY,0,@Run_date),0)) -- End of Run_Date month a year ago
In my Selct statement I have
.......
Where convert(datetime,convert(varchar(10),i.[InvoiceDate],120)) < @Edge_Date
in SQL Server database [InvoiceDate] field is (int , not null) type and in this format 20180223
20170904
20171228
..........
I assume that this conversion is not proper one.
select cast(cast( 20171228 as varchar) as date)
if you have try_cast, use that:
select try_cast(cast( 20171228 as varchar) as date)
or, with convert style 112 for yyyymmdd
select convert(date,cast( 20171228 as varchar),112)
select try_convert(date,cast( 20171228 as varchar),112)