date data type format change
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=299
Printed Date: 30 Apr 2024 at 2:59am
Topic: date data type format change
Posted By: amey153
Subject: date data type format change
Date Posted: 09 Mar 2007 at 2:20am
|
I am using a parameter of type date in my report which uses the format yyyy-mm-dd.
The function currentdate() returns the date in the format dd-mm-yyyy.
As a result, I am not able to scedule the report on a daily basis.
Please suggest me some way to attain synchronism between the two date formats.
How can I define a function returning the date in a specified format?Alternatively,
How can I change the default date date type format.
|
Replies:
Posted By: hilfy
Date Posted: 13 Mar 2007 at 10:20am
|
I suspect that the parameter is actually a string which contains a date. I that case, you can use something like this to get CurrentDate into the correct format:
to_text(CurrentDate, 'yyyy-MM-dd')
(NOTE: The format string is CASE SENSITIVE!!!!)
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: amey153
Date Posted: 16 Mar 2007 at 6:42am
|
Thanks for the reply.
But, nowhere is the text used.
variable A (parameter passsed) =return valure of function currentdate() dd-mm-yyyy
variable B (parameter type) = date (built-in) yyyy-mm-dd
These two are not compatible
|
Posted By: hilfy
Date Posted: 16 Mar 2007 at 12:44pm
|
If they're both of type Date, then it shouldn't matter what the format is, because the Date type should handle the translation between the formats.
Can you explain a little more about what you're trying to do? Is the date format particular to your database?
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: amey153
Date Posted: 21 Mar 2007 at 7:04am
|
I am passing a parameter to a SQL statement of type date (yyyy-mm-dd by default).
I want to pass it using currentdate() function. (dd-mm-yyyyy by default)
But, the crystal server itself refuses to accept this with the error:
"Enter the date in yyyy-mm-dd format".
Is it possible at all possible to use a crystal syntax function when specifying a parameter.
The database is not the reason for the error. Crystal reports refuses to accept the parameter value that I pass.
|
Posted By: hilfy
Date Posted: 21 Mar 2007 at 7:50am
|
Hmm...I've never encountered this error before (and I've been working in Crystal for over 10 years....) I would try passing it the results of the formula I wrote above that converts the date to a string. It's a string instead of a date, but it's in the format that the server is looking for, so it may work. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: amey153
Date Posted: 21 Mar 2007 at 8:02am
|
What I have tried is changing the parameter type to string and then using SQL statement to convert the database date field to a string as well.
So if I pass a string like "20070321", the report is generated.
But, how do I incorporate the formula mentioned by you in the the SQL statement?
I have written a formula that converts the currentdate() to a string"yyyyMMdd"
Can we use a formula field in the SQL select statement?
Just for information, I am using a cross-tab in my report, so cannot use a seect record formula.
I really appreciate the pains you are taking to read and reply to my problems. You have an experience of 10 years and I don't have even of 10 weeks.
 |
Posted By: BrianBischof
Date Posted: 21 Mar 2007 at 10:21am
|
Hey there - I'll jump in here for a minute, but not sure how much I can help. First off, are you using a SQL Server database or a different database. CR can pass date parameters to SQL Server with no problem. So if this is a SQL database, then something else must be going wrong. Secondly, the record selection formula is totally independent of whether the report is using a cross-tab object or not. The cross-tab is just another object you add to the report (like a text object or a field) and you can have a record selection formula on the report. Third, you keep saying that SQL wants the date in a certain format, but then later you say that the database isn't the problem with the date, it's CR that isn't accepting the date. That sounds contradictory. It also makes me think that maybe you are using a .NET app to pass the date to the report object instead of typing it in. But you don't mention .NET at all so maybe I"m wrong? Are you typing in the date parameter or passing it via code? ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: amey153
Date Posted: 22 Mar 2007 at 2:04am
|
"Are you typing in the date parameter or passing it via code?"
I don't understand this and I feel this is the solution to my problem.
CR is made to prompt for a parameter of type date.
Can I pass currentdate() instead of selecting a date from the calender? I am facing problems (with the format) when I do this.
That is why I say the problem is not with the database ( am using ORACLE- CR connects using ODBC)
|
Posted By: BrianBischof
Date Posted: 22 Mar 2007 at 10:59am
|
Are you using a .NET WinForm?
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: rclowe
Date Posted: 25 Feb 2011 at 4:44am
|
It looks like this was never resolved, and I am running into the same issue. I am sure I will find out soon enough if it is preferable to revive this old thread or if I should create a new one, so I will start the process by attempting the former.
I have been using CR 8.5 with Sage MAS 90 3.71 and 4.05 and CR 10 with Sage MAS 90 4.10, 4.20 and 4.30. Adding a Date Parameter Field in CR 8.5 and 10 and the date showing in the Enter Parameter Values dialog appears as mm/dd/yyyy.
However, now in CR XI when I add a Date Parameter (Static) field it has the following text:
'Please enter Date in format "yyyy-mm-dd".'
I just spoke to someone who indicated that her client is unhappy with this change and we are looking for a way within Crystal to have the system allow dates to be entered in the old format of mm-dd-yyyy.
It should be mentioned that there is a Calendar Widget that can be clicked on to use a graphical tool to select the date or dates, but apparently these customers do not want to use that. Also, my CR XI R2 is on SP6.
Thanks,
rclowe
|