Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Date Error: Date comes in as a string Post Reply Post New Topic
Author Message
mrtj
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 26
Quote mrtj Replybullet Topic: Date Error: Date comes in as a string
    Posted: 21 Aug 2013 at 8:16am
2013-08-01 in the data file is August 1st 2013.

When I build a new report I want the field type to call this a date and not a string. I don't want to have to convert in the program. That is overkill.

I tried to change overall options so that "DATE" format was not the system default but instead custom
YYYY MM DD, with a separator of a dash '-"
Then I build a new report as a test starting all over thinking this would trick crystal report this field as a date, but no , it is still string.

How to simplify?
SQL and Excel both have no issues with this field being already defaulting as a string
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 21 Aug 2013 at 10:08am
Simple answer, you can't.  You have to convert it to the date in Crystal if you want to use it like a date. 
 
-Dell
IP IP Logged
mrtj
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 26
Quote mrtj Replybullet Posted: 22 Aug 2013 at 4:14am
Monthname(tonumber(mid({CalendarDropDates.DropDate},6,2))) ;

errors out "the string is non-numeric"

2013-08-01 in data
Where mid of 6 for 2 would be an 08
then to number of a string 08 would return an 8
then monthname of an 8 would return August

but no such luck crystal "sxcks"
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 22 Aug 2013 at 4:30am
Break down the formula - put just mid({CalendarDropDates.DropDate}, 6, 2) on your report and see what the value is.  Then add "ToNumber" to it and check the value again. 
 
Is there any possibility that the field is blank or null?  That might also be causing this error.  You could try this:
 
if not IsNull({CalendarDropDates.DropDate}) and {CalendarDropDates.DropDate} <> "" then
Monthname(tonumber(mid({CalendarDropDates.DropDate},6,2)))
 
-Dell
IP IP Logged
mrtj
Newbie
Newbie


Joined: 26 Mar 2013
Online Status: Offline
Posts: 26
Quote mrtj Replybullet Posted: 22 Aug 2013 at 10:07am
I cheated and went to SQL, changed date to datetime, all ispeachy fine thanks for your help
IP IP Logged
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.047 seconds.