crystal reports date format issue

magicdress
01-29-2004, 10:06 AM
hi all, distraught, disgruntled and distressed. i really hope this is something simple and i've just been doing all kinds of crazy gymnastics because the trees were blocking my forest.

i'm creating a periodic report (dates given as params) but the data that i have to compare to the selected dates is of type 'number' on a remote as400.

i've been using string manipulations to get the date parts out and then the date conversion to put them back together to compare with the parameters. but at that point i get the error: "too many arguments have been given to this function". the month day and year formula fields are working properly...what gives?

(crystal syntax)
CDate ({@TESTYR}, {@TESTMO}, {@TESTDY})
@TESTYR =
Left (Right (REPLACE(REPLACE(CSTR({WRSCONV.WRDTRC}),",",""),".",""), 6), 4)
@TESTMO =
IF LEN(REPLACE(REPLACE(CSTR({WRSCONV.WRDTRC}),",",""),".","")) = 9 THEN
Left (REPLACE(REPLACE(CSTR({WRSCONV.WRDTRC}),",",""),".",""), 1)
ELSE
Left (REPLACE(REPLACE(CSTR({WRSCONV.WRDTRC}),",",""),".",""), 2)


@TESTDY =
Left (Right (REPLACE(REPLACE(CSTR({WRSCONV.WRDTRC}),",",""),".",""),8 ),2 )

(/crystal)

i'm also trying various SQL date conversions but it doesn't like this:

SUBSTRING(CAST(WRSCONV."WRDTRC" AS CHAR), 5, 4) because of the casting, i believe.

the data is coming from server at another agency, so i haven't any control over the format :(

am i making this more difficult than it needs to be? am i going to be embarrassed tomorrow that i even posted this because it's such a no-brainer (i hope!)?

TIA!
becky

MKoslof
01-29-2004, 11:59 AM
Can you please tell me what this field looks like originally: WRSCONV.WRDTRC

And what your final cast is supposed to look like: dd/mm/yyyy. etc.

That function is way too complex and ha way too many operators :). There is a much easier way to do this. Have you looked at the DateTime functions of Crystal? Also, Crystal has many build in conversion tools..if you look at the formula editor there are tons listed.

But, let me know the initial string value (ex: 532433, whatever) and its final output...I'll let you know the best formula to use.

magicdress
01-29-2004, 12:04 PM
Can you please tell me what this field looks like originally: WRSCONV.WRDTRC

And what your final cast is supposed to look like: dd/mm/yyyy. etc.

That function is way too complex and ha way too many operators :). There is a much easier way to do this. Have you looked at the DateTime functions of Crystal? Also, Crystal has many build in conversion tools..if you look at the formula editor there are tons listed.

But, let me know the initial string value (ex: 532433, whatever) and its final output...I'll let you know the best formula to use.

today's date would be 01292004 in my as400 table. i'm not concerned with the final cast, i just need to be able to compare with my date parameters.

THANK YOU FOR YOUR HELP (i've been shouting and cursing --co-workers and looking worried)

MKoslof
01-29-2004, 04:20 PM
OH, so this format is consistent. Well you don't need a string cast here, because 01292004 is the exact date...if you did a real "string" conversion, this would not produce 01/29/2004 because the string is being evaluated and then reset into a date..so the result would actually be something like 02/22/1980.

As an example, create a formula field right next to your database field..this way you can see how the final result formats in the correct manner. Place this formula on your report on the details section next to the database field in question, it will populate just like the database field. Refresh the report to see the results. In the selection editor of the forumula use this:




stringVar newDate;

newDate:=Mid({numTable.date},1,2) & "/" & Mid({numTable.date},3,2) & "/" _
& Mid({numTable.date},5,4);

CDate(newDate)



this parse out the 01292004 string and reformat it...and then, as its final output newDate is put through the CDate conversion. Now this formula field can be run against any valid Date parameter.

magicdress
01-30-2004, 09:28 AM
OH, so this format is consistent. Well you don't need a string cast here, because 01292004 is the exact date...if you did a real "string" conversion, this would not produce 01/29/2004 because the string is being evaluated and then reset into a date..so the result would actually be something like 02/22/1980.

As an example, create a formula field right next to your database field..this way you can see how the final result formats in the correct manner. Place this formula on your report on the details section next to the database field in question, it will populate just like the database field. Refresh the report to see the results. In the selection editor of the forumula use this:




stringVar newDate;

newDate:=Mid({numTable.date},1,2) & "/" & Mid({numTable.date},3,2) & "/" _
& Mid({numTable.date},5,4);

CDate(newDate)



this parse out the 01292004 string and reformat it...and then, as its final output newDate is put through the CDate conversion. Now this formula field can be run against any valid Date parameter.

well, i misspoke. the date is a variable length numeric type:

1302004
or
12302003

i DO know, however, that a date of 1032003 is actually 1/3/03 and not 10/3/03 because i formatted the data to upload to them...so that's something, anyway!

currently i'm working on doing the processing in my SQL statement and a view on the 400 but even given that, i'm still getting 'too many

ACK!! I GOT IT JUST AS I WAS WRITING THIS!!!

okay, this is the view i created on the 400 (i have to share it--this was a 3-day stumper for me!!)

CREATE VIEW WRSLIB.WARRPT (
wrtype FOR COLUMN TYPE,
wrcas# FOR COLUMN DOCKET,
wrdfnt FOR COLUMN NAME,
wrssn FOR COLUMN SSN,
wrchrg for column CHARGE,
BYR FOR COLUMN BYEAR ,
BMO FOR COLUMN BMONTH ,
BDY FOR COLUMN BDAY,
RCDYR FOR COLUMN RYEAR ,
RCDMO FOR COLUMN RMONTH ,
RCDDY FOR COLUMN RDAY
)
AS
SELECT wrtype, wrcas#, wrdfnt, wrssn, wrchrg,

CASE
WHEN LENGTH(RTRIM(cast(wrdob as char(8)))) = 8
THEN
substr(cast(wrdob as char(8)),5,4)
WHEN LENGTH(RTRIM(cast(wrdob as char(8)))) = 7
THEN
substr(cast(wrdob as char(8)),4,4)
END BYR,

CASE
WHEN LENGTH(RTRIM(cast(wrdob as char(8)))) = 8
THEN
substr(cast(wrdob as char(8)),1,2)
WHEN LENGTH(RTRIM(cast(wrdob as char(8)))) = 7
THEN
substr(cast(wrdob as char(8)),1,1)
END BMO,

CASE
WHEN LENGTH(RTRIM(cast(wrdob as char(8)))) = 8
THEN
substr(cast(wrdob as char(8)),3,2)
WHEN LENGTH(RTRIM(cast(wrdob as char(8)))) = 7
THEN
substr(cast(wrdob as char(8)),2,2)
END BDY,

CASE
WHEN LENGTH(RTRIM(cast(wrdtrc as char(8)))) = 8
THEN
substr(cast(wrdtrc as char(8)),5,4)
WHEN LENGTH(RTRIM(cast(wrdtrc as char(8)))) = 7
THEN
substr(cast(wrdtrc as char(8)),4,4)
END RCDYR,

CASE
WHEN LENGTH(RTRIM(cast(wrdtrc as char(8)))) = 8
THEN
SUBSTR(cast(wrdtrc as char(8)),1,2)
WHEN LENGTH(RTRIM(cast(wrdtrc as char(8)))) = 7
THEN
SUBSTR(cast(wrdtrc as char(8)),1,1)
END RCDMO,

CASE
WHEN LENGTH(RTRIM(cast(wrdtrc as char(8)))) = 8
THEN
substr(cast(wrdtrc as char(8)),3,2)
WHEN LENGTH(RTRIM(cast(wrdtrc as char(8)))) = 7
THEN
substr(cast(wrdtrc as char(8)),2,2)
END RCDDY

FROM WRSLIB.WRSMAST WHERE WRSPCD LIKE 'P%'


then the following date conversion in crystal:

Date({WRSCONV.RCDMO} & "/" & {WRSCONV.RCDDY} & "/" & {WRSCONV.RCDYR})

W00T!

MKoslof
01-30-2004, 09:40 AM
be careful with this:

Date({WRSCONV.RCDMO} & "/" & {WRSCONV.RCDDY} & "/" & {WRSCONV.RCDYR})

You are telling Crystal to explicitally case this concatanated string into a Date. Make sure all combinations of this work. such as 10/10/2004 and 01/10/2004 do not produce the same results(or completely different, wrong results).

If you want to do all your casting on the SQL side, you are better off just casting your value into a valid date within SQL..if you have come this far, why stop now. Cast your value as a valid date within SQL and do nothing in Crystal.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum