convert text type yyyymmdd to date type

Taras
Taras used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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)

Author

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

Author

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.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

Commented:
date is coming out as 2018-09-25 00:00:00:000.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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)
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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)

Author

Commented:
Do I need to use this select .....or just cast(convert(varchar,@Edge_Date,112) as int)

Author

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)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

Commented:
Thank you PortletPul
that cast(convert(Char(8),@edge_Date,112) as int) did job.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial