Print Page | Close Window

Converting String to Date Record Selection

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15738
Printed Date: 02 May 2024 at 10:48pm


Topic: Converting String to Date Record Selection
Posted By: cant2ny
Subject: Converting String to Date Record Selection
Date Posted: 28 Feb 2012 at 4:07am
I realized I posted this in the wrong forum yesterday:

Is it possible to convert a string to date during the record selection process? I have a table in my SQL database that is of type VarChar and stores the date as 20120227. I cannot convert this to Date on the DB side due to other factors. Is it possible to convert this VarChar field to a date during Record Selection (to be compared against a Date CR parameter)? There is no formatting needed as that's handled elsewhere, I just need to convert it during record selection to use against the Date CR Parameter.

The only way I can think to do it is to use a SQL command but would like to avoid that since I'll need to do redo the entire report.


Replies:
Posted By: DBlank
Date Posted: 28 Feb 2012 at 4:12am
you can convert it in the select statement itself. Here is an example:
 
date(mid({table.strindate},5,2)+'/'+right({table.strindate},2)+'/'+left({table.strindate},2)) in {?StartDate} to {?Enddate}

Posted By: cant2ny
Date Posted: 28 Feb 2012 at 4:44am
Thanks, however I get a "Bad date Format String" when running a preview. I also adjusted the left({table.strindate},2) to include 4 places. Looks correct in the details section.

Would this be caused by the NULL and "" values in the database?

Posted By: DBlank
Date Posted: 28 Feb 2012 at 7:20am
yes.
I assumed you had valid entries in each row.
you can try to add anopther condition that will exclude your '' or null values
 
isdate(mid({table.strindate},5,2)+'/'+right({table.strindate},2)+'/'+left({table.strindate},2)
and
date(mid({table.strindate},5,2)+'/'+right({table.strindate},2)+'/'+left({table.strindate},2)) in {?StartDate} to {?Enddate}
 

Posted By: cant2ny
Date Posted: 28 Feb 2012 at 10:03am
Thanks, I had to scrap trying to salvage the report and ended up having to do the query for the conversion due to other factors.

Posted By: Motib
Date Posted: 21 Feb 2013 at 5:50pm
hi all,
i have a varchar type in sql and it is stored like 22/1/2012. but i want to get opening balance of qty....... if i write "If {DDate} < {?From Date} then "Qty"" but it will showing me 0.
plz help me is this write condition or not.


Print Page | Close Window