How do I find the 10th of the month after the purchase date

[KayKay]

sql2k
cr9

I need to be able to find the 10th of the month following the purchase date (supplied from table).

Is there a simple formula for this?

Please help

KayKay

 

[lbass]

The following will give you the 10th of the month for the month following the purchase date.

dateadd("m",1,{table.purchdate}-day({table.purchdate})+10)

Or did you want the NEXT 10th of the month, even if it is in the same month?

-LB

 

[KayKay]

I think that this will work just fine.

If I do want the next month would I need to check the purchase and do something like this?

if day({table.purchdate})<=10 then
dateadd("m",2,{table.purchdate}-day({table.purchdate})+10)

KayKay

 

[KayKay]

I just read what I wrote and realize that it makes no sense, sorry. What I meant to ask is:

If {table.purchdate} <=10 (ex.01/02/05)
how do I get the due date to be 01/10/05?

Thanks again

 

[dgillz]

try this:

if day({Table.purchdate})<=10 then date(year({Table.purchdate}),Month({Table.purchdate}),10) else
dateadd("m",1,{table.purchdate}-day({table.purchdate})+10)



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

You could use:

if day({table.purchdate}) <= 10 then
{table.purchdate}-day({table.purchdate}) + 10 else
dateadd("m",1,{table.purchdate}-day({table.purchdate})+10)

-LB

 

[KayKay]

Thank you both, dgillz and lbass so much. This is exactly what I needed!

KayKay