convert text type yyyymmdd to date type

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.
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
You can cast the int to varchar and as long as it is yyyymmdd then it will cast to date

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)
0
TarasAuthor Commented:
Hi Paul I tried your suggestion select cast the same as it was before report run but no data. I will try now convert to 112
0
TarasAuthor Commented:
if run this in SQL Server and put hard coded date as;
Set @Run_date = '2018-09-25'
 Set @Edge_Date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))  


 -- Range 1:........
....
It run ok and give me records out.
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

TarasAuthor Commented:
date is coming out as 2018-09-25 00:00:00:000.
0
PortletPaulEE Topic AdvisorCommented:
You state that the column is an integer, so here it is with an integer column:

with sample as (
   select cast(20171228 as int) intcol
   )
select try_cast(cast( intcol as varchar) as date)
from sample

This works (and all of them will work)
0
PortletPaulEE Topic AdvisorCommented:
IF you are asking for the REVERSE of this, then calculate the dates for you date ranges and convert those to integers


declare @Run_date date = '2018-09-25'
declare @Edge_Date date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))  
 
select cast(convert(varchar,@Edge_Date,112) as int)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Do I need to use this select .....or just cast(convert(varchar,@Edge_Date,112) as int)
0
TarasAuthor Commented:
To go back to beginning to clarify things.

If I run my sql command without and selection criteria on SQL Server Management Studio I got the “Date” out as 20180928 as date is “int” data type in my Sql Server table.

In my sub report based on the same command I am passing date parameter from main report  ?Run_Date.
That date parameter user get by date picker I assume this date is date/time format.
So I assume I have to change one of those types that they could match in sql command – sub reports.

Is your suggestions to change parameter date type to integer (int) like this?
  cast(convert(varchar,@Edge_Date,112) as int)
0
PortletPaulEE Topic AdvisorCommented:
Is your suggestions to change parameter date type to integer (int) like this?
  cast(convert(varchar,@Edge_Date,112) as int)

Yes.

Typically if dates are stored as integers it will be much more efficient to filter using integers (so that you avoid converting each tow vakue to date).

I dont know what data type is returned by your datepicker.
0
TarasAuthor Commented:
Thank you PortletPul
that cast(convert(Char(8),@edge_Date,112) as int) did job.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.