Avatar of Taras
Taras
Flag for Canada asked on

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.
Crystal Reports

Avatar of undefined
Last Comment
Taras

8/22/2022 - Mon
PortletPaul

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)
Taras

ASKER
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
Taras

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Taras

ASKER
date is coming out as 2018-09-25 00:00:00:000.
PortletPaul

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)
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Taras

ASKER
Do I need to use this select .....or just cast(convert(varchar,@Edge_Date,112) as int)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Taras

ASKER
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)
PortletPaul

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.
Taras

ASKER
Thank you PortletPul
that cast(convert(Char(8),@edge_Date,112) as int) did job.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy