Sage dates in Crystal Reports

Posted By SaskPeter over 8 years ago

I am doing pwFormatDate(ORDDATE) = CurrentDate -1 to obtain yesterdays date and then trying to filter on this date for yesterdays orders. But I need to convert this date back to a Sage date to do the filter. Is there a function that converts back to Sage format? Or is there another way of doing this?

Any clues will be helpful.

Thanks

0 Jay Converse Acumen over 8 years ago

{OEORDH.ORDDATE} = tonumber(tostring({datefield},"yyyyMMdd"))
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Mike Cook over 8 years ago in reply to Jay Converse Acumen

or use the datediff() function.

something like if datediff("d", pwformatdate(ORDDATE),CurrentDate)=1 then xxx else xxx
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jay Converse Acumen over 8 years ago in reply to Mike Cook

Note that using pwformatdate() in a record retrieval is very inefficient, because Crystal has to retrieve all the records before evaluating the function. Therefore, never do a selection criteria as

pwformatdate({OEORDH.ORDDATE}) in {dateparm1} to {dateparm2}

do it as

{OEORDH.ORDDATE} in tonumber(tostring({dateparm1},"yyyyMMdd")) to tonumber(tostring({dateparm2},"yyyyMMdd"))

and then Crystal will send the correct SQL query and retrieve much fewer records. The difference in speed on large databases can be over 1000 to 1.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 SaskPeter over 8 years ago in reply to Jay Converse Acumen

Thank you kindly gentlemen
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Shane - Ascend Business Solutions over 8 years ago in reply to Jay Converse Acumen

{OEORDH.ORDDATE} = tonumber(CStr({datefield},"yyyyMMdd"))
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 300

Sage dates in Crystal Reports

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING