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

[Former Member]

Hi Robert,

Please insert the following formula in dt

numbervar dd;

Numbervar mm;

numbervar yy;

dd:=tonumber(right(totext(appointments.appt_date,"##"),2));

mm:=tonumber(mid(totext(appointments.appt_date,"##"),5,2));

yy:=tonumber(left(totext(appointments.appt_date,"##"),4));

cdate(yy,mm,dd)

Thanks,

Sastry

[Former Member]

Hi Robert,

You could also use this expression in Selection criteria

appointments.appt_date = totext(dateadd("d", 2, currentdate),"yyyyMMdd")

[Former Member]

Sastry

When i paste that code into dt it gives me an error saying i need a ) at appointments.appt_date.

This is the whole forumla correct?

numbervar dd;

Numbervar mm;

numbervar yy;

dd:=tonumber(right(totext(appointments.appt_date,"##"),2));

mm:=tonumber(mid(totext(appointments.appt_date,"##"),5,2));

yy:=tonumber(left(totext(appointments.appt_date,"##"),4));

cdate(yy,mm,dd)

[Former Member]

appointments.appt_date = totext(dateadd("d", 2, currentdate),"yyyyMMdd")

will not work as you will be comparing numeric to text, if appt_date is numeric indeed.

If appt_date is numeric then the easiest way would be to convert the above.

Robert:

In your selection criteria, just use the below:

appointments.appt_date = tonumber(totext(dateadd("d", 2, currentdate),"yyyyMMdd"))

[Former Member]

Hi

Please try this

numbervar dd;

Numbervar mm;

numbervar yy;

dd:=tonumber(right(totext({appointments.appt_date},"##"),2));

mm:=tonumber(mid(totext({appointments.appt_date},"##"),5,2));

yy:=tonumber(left(totext({appointments.appt_date},"##"),4));

cdate(yy,mm,dd)

Thanks,

Sastry

[Former Member]

Sastry,

Thanks for your help. Am a bit confused as to where i put this formula. I created a new one called DT and then pasted what you suggested. When I throw that into the details section, it gives me the date 11/28/2008. Then i went to select expert and tried the second part but DT doesnt show up. Sorry I am thoroughly confused at this point.

Rob

[Former Member]

Hi Robert,

It is because the field you have in database is not a date field. It is a numberic field. Write the following formula and check your current date against this formula

Formula @dt

-

---

numbervar dd;

Numbervar mm;

numbervar yy;

dd:=tonumber(right(totext(20081228,"##"),2));

mm:=tonumber(mid(totext(20081128,"##"),5,2));

yy:=tonumber(left(totext(20081228,"##"),4));

cdate(yy,mm,dd)

--

Now in record selection you check like @dt = dateadd('d'm+2,currentdate)

This will get you the results.

Thanks,

Sastry