Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: date data type format change Post Reply Post New Topic
Page  of 2 Next >>
Author Message
amey153
Newbie
Newbie
Avatar

Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
Quote amey153 Replybullet 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 IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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 IP Logged
amey153
Newbie
Newbie
Avatar

Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
Quote amey153 Replybullet 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 IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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 IP Logged
amey153
Newbie
Newbie
Avatar

Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
Quote amey153 Replybullet 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 IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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 IP Logged
amey153
Newbie
Newbie
Avatar

Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
Quote amey153 Replybullet 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.Tongue 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
amey153
Newbie
Newbie
Avatar

Joined: 09 Mar 2007
Location: India
Online Status: Offline
Posts: 7
Quote amey153 Replybullet 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 IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.