Solved

Crystal Report Convert String to Date

Posted on 2012-04-08
7
1,217 Views
Last Modified: 2012-04-08
I declared a column in SQL table to varchar and the parameter in Crystal Report (asp) to Date.
Parameter1 is called DateFrom (Date)
Parameter 2 is called DateTo (Date)
I also created a formula in the Record:

ToText({Table.Date},"MM/dd/yyyy") >= {?DateFrom}
 and
ToText({Table.Date},"MM/dd/yyyy")<= {?DateTo}

I tried to convert the Table.Date to Date format and then use the parameter but it gives me an error like: "A string is needed"
The main reason I did this was that I did not want to use the DateTimeFormat in SQL and ONLY WANTED TO HAVE THE MM/DD/YYYY instead of THE Date and Time.

PLEASE HELP. This is driving me crazy.
FYI: I am using ASP.NET C# and this is my first asp, Crystal project so please be clear in your comments.
0
Comment
Question by:pauledwardian
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37821064
You parameters and the values you are comparing them to must be th same data type.  You cannot have one as text and the other as date as in your post.

But since you are using varchar in your database aren't  the values already 'text'?  What datatype does Crystal see the date field as ?

If the dates are regarded by Crystal as text,  then unless they are already in a format that can be compared you will have to do two levels of conversion.  

I think we need to see some sample ' date ' values from the database.
0
 
LVL 20

Assisted Solution

by:BuggyCoder
BuggyCoder earned 166 total points
ID: 37821103
convert specific sections and form date:-
CDate (
(ToNumber ({TextDateField} [1 to 4])),
(ToNumber ({TextDateField} [5 to 6])),
(ToNumber ({TextDateField} [7 to 8]))
)

refer here:
http://www.forumtopics.com/busobj/viewtopic.php?t=171929&sid=7c117f8d8872d20afe1f5a95a66bf8a0
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 334 total points
ID: 37821290
Nice try buggy but the format of his field is mm/dd/yyyy

Try it this way

CDate (
(ToNumber ({TextDateField} [5 to 8])),
(ToNumber ({TextDateField} [3 to 4])),
(ToNumber ({TextDateField} [1 to 2]))
)

mlmcc
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pauledwardian
ID: 37821777
I got this error: Please look at the attachment. I also changed my SQL dateformat to TEXT instead of Varchar:
This also includes a screenshot of the setting I used to format thew parameters:
Crystal Settings
0
 

Author Comment

by:pauledwardian
ID: 37821900
Never mind. I didnt verify the database after making the changes that was why it was giving me the error.
Now a differant question:
On the Report Preview in visual studio I can see the Refresh button on the report but when I compile the application and run the report it does not exit there.
How can I add that feature on the report OR how can I add a button in my C# page to Refresh the report?

Paul
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 334 total points
ID: 37821951
I have n't used that viewer but check the properties.  Each button usually has an ENABLE property

mlmcc
0
 

Author Closing Comment

by:pauledwardian
ID: 37822234
Thanks!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now