i have 2 date fields and want to be able to calculate the no months between the 2 dates - does any one have any sample code of ideas of how to achieve this?
What do you want to do with the answer? Do you want to store it, display it, use it in reporting, or do you need it for another calculation?
I just want to display the answer in a field on screen
I would use a SQL Job that runs once a day, or a couple of times during the day which runs a SQL Script along the lings of (Possibly could be run as an Escalation Rule depending on the table being updated)
SET <Month Field> = DateDiff(Month,StartDate,EndDate)
WHERE <Whatever parameter is met)
Do this all the time for things like Equipment and I need to track the age of it.
thanks for the reply Matthew - ideally i need it to run on change of entering the end date so that i can then try to add a formula which works out the cost based on the no of months
If you want to store the value, then your approach can be different. Create the field as an integer field. Create a table script that runs ONLY when these 2 date fields have a value. If they do have a value (CRM has already validated them), then you can run a simple SQL command via the tablescript that does a simple datediff and sets the value in the field.
You don't need a SQL job. You shouldn't need a trigger. I'd still have the field a readonly field on the screen because you don't want your user manually changing it. I'd go with the second method.