Solved

Crystal Report Convert String to Date

Posted on 2012-04-08
7
1,352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Join Number Parameter with commas 8 35
Access-Control-Allow-Origin 4 40
EF5: Update Model from Database not working 3 71
What is needed to become a DBA? 7 46
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 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