troubleshooting Question

convert text type yyyymmdd to date type

Avatar of Taras
TarasFlag for Canada asked on
Crystal Reports
10 Comments1 Solution103 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros