Bad Date format error

[pl1101]

I have 3 fields and I need to convert them to a date and use a parameter.
scamm
scadd
scayy

when I do this then I get the "Bad date format string" error.

I used the date(totext,0,"") functions.
Crystal XI release2 and AS400

 

[fisheromacse]

did you check for nulls?
is it possible that some of the values are not legitimate date values?

you can add a check for nulls to your formula:

IF isnull({Table.Field}) or TRIM({Table.Field})="" then Date(0,0,0) else ...

 

[IanWaterman]

Correct formula would be

Date(scayy, scamm, scadd)

or if numeric strings


Date(tonumber(scayy), tonumber(scamm), tonumber(scadd))

Ian

 

[fisheromacse]

ian,
ahh, i didn't read as 3 field to make one date, but as 3 fields each containing a date.
good catch!

 

[pl1101]

I didn't realize that I didn't have to convert. The fields are numbers. The problem is that the year is 2 digits.
How do I make 10.00 "2010" and 8.00 "2008" and 0.00 to 2000 for the date?

if {LQSPCDT2.SCACYY} IN [1 to 9999] then
date({LQSPCDT2.SCACYY}, {LQSPCDT2.SCACMM}, {LQSPCDT2.SCACDD})
This formula works but it doesn't know what to do with 0.00 and the others show up as m/d/y
example: 2/5/8

I am trying to use this date as a parameter and choose dates to select on, but the date is not being recognized.

 

[fisheromacse]

First check the format of the display field in the report to make sure it is what you desire.
right click on the field, choose "Format Field", then the "Date" tab.

Then, check the length of the year field and then process accordingly:

//{@YYYYMMDD}
numbervar yylen := len(TRIM({LQSPCDT2.SCACYY}));
numbervar yyval;

if yylen = 1 then yyval := "200"&{LQSPCDT2.SCACYY})
else
if yylen = 2 then yyval := "20"&{LQSPCDT2.SCACYY});

date((yyval, {LQSPCDT2.SCACMM}, {LQSPCDT2.SCACDD})

 

[pl1101]

I get an error " a string is required here"

numbervar yylen := len(TRIM({LQSPCDT2.SCACYY}));
numbervar yyval;
if yylen = 1 then yyval := "200"&{LQSPCDT2.SCACYY} else
if yylen = 2 then yyval := "20"&{LQSPCDT2.SCACYY};
date(yyval, {LQSPCDT2.SCACMM}, {LQSPCDT2.SCACDD})

---it highlights the "len(TRIM({LQSPCDT2.SCACYY}));

 

[fisheromacse]

oops. sorry change this...
numbervar yylen := len(TRIM(totext({LQSPCDT2.SCACYY})));

 

[lbass]

If the year numbers are always for year 2000 or later, you can just use:

date(2000+{LQSPCDT2.SCACYY},{LQSPCDT2.SCACMM}, {LQSPCDT2.SCACDD})

-LB

 

[pl1101]

Now is says date must be between 1 and 9999. I added another if statement

numbervar yylen := len(TRIM(totext({LQSPCDT2.SCACYY})));
numbervar yyval;
if yyval in [1 to 9999] then
if yylen = 1 then yyval := 200+{LQSPCDT2.SCACYY} else
if yylen = 2 then yyval := 20+{LQSPCDT2.SCACYY};
date(yyval, {LQSPCDT2.SCACMM}, {LQSPCDT2.SCACDD})

But I still get this error message

 

[lbass]

Did you try my suggestion?

-LB

 

[pl1101]

The year has some 1990's also...

 

[lbass]

What does the year field look like when the year is supposed to be in the 90's?

-LB

 

[pl1101]

if the year should be 1995 then it is stored in the database as 95.00 (number)

 

[lbass]

Not sure how high your 2000 years go, but you could try this:

if {LQSPCDT2.SCACYY} > 50 then
date(1900+{LQSPCDT2.SCACYY},{LQSPCDT2.SCACMM}, {LQSPCDT2.SCACDD}) else
date(2000+{LQSPCDT2.SCACYY},{LQSPCDT2.SCACMM}, {LQSPCDT2.SCACDD})

-LB

 

[pl1101]

Well this looks like it is working, but now I get an error "A day number must be between the number of the days in the month". How do you check for the days of the month? Thank you sooo much for the assistance.

 

[lbass]

What is the value of the day when you get that message? Is it null or zero? Just place the field next to the formula. What do you want to happen if the day = 0? And why would it? Also, what are the values of the year and month fields when you get this error?

-LB