| Author |
Message |
amey153
Newbie
Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
|
   Topic: date data type format change
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.
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
amey153
Newbie
Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
|
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
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
amey153
Newbie
Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
|
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.
Edited by amey153 - 21 Mar 2007 at 7:04am
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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
|
|
|
|
IP Logged |
|
amey153
Newbie
Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
|
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.  |
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
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>
|
|
IP Logged |
|
amey153
Newbie
Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
|
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)
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
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>
|
|
IP Logged |
|
|
|
