DateAdd Function Help

[Former Member]

Good Morning,

I have a report right now that displays all the appointment times for a doctor. I need to create a report that looks for appointments 2 days ahead of the current date. I.E. If i run the report on a monday, i will get wednesdays appointments.

I have tried going to my select expert and pick appointments.appt_date then picked formula and tried both

appointments.appt_date = 'currentdate' +2

and

appointments.appt_date =dateadd(u201Cdu201D, 2, currentdate)

The bottom formula works if i put it in the details and will return the correct date. But how do i select the records using this formula? Also my appointments field is formatted as 20081223. I suspect that maybe my select statement isnt working because the dates are in this format? If i right click that field and pick format field there isnt a date tab. Sorry to be such a noob, but I could really use some help on this.

Thanks

Ringrim

Edited by: Robert Ingrim on Dec 23, 2008 6:38 PM

[Former Member · Accepted solution]

Hi Robert

Follow these steps:

1. Open the report in Crystal Reports. Goto Reports menu->Selection Formulas->Click Record. This will open up the Record Selection Formula Editor.

2. Write the following formula there:

stringVar strDate:=mid(cstr(<YourDateField>),1,2) & mid(cstr(<YourDateField>),4,3) & mid(cstr(<YourDateField>),8,3);

dateVar changedDate := cdate(mid(cstr(strDate),1,4) &"/"& mid(cstr(strDate),5,2) &"/"& mid(cstr(strDate),7,2));

stringVar newDate := cstr(dateadd("d",2,changedDate));

numberVar DateToCompare := tonumber(mid(cstr(newDate),7,4) & mid(cstr(newDate),1,2) & mid(cstr(newDate),4,2));

<YourDateField>=DateToCompare;

3. Save and Close the formula and run th report.

4. Please note that this formula will only work for your date field (which appears to be a number) where the date is in YYYYDDMM format.

Hope this helps.

Regards

Nikhil