Calculate no of months


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) 

    UPDATE <Table>

    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

  • Method 1.

    You don't need to do this. You don't need to store the value if it is for display purposes only. You can just create a text field of size 1. It isn't going to actually store anything at all. It is a placeholder on the screen as a target for a javascript. You add that field to the screen. In the create script you make it readonly. Then in a custom content script, you just have a calculation using easily findable javascripts from the internet that can do a datefdiff between 2 dates... BUT, you will hit problems though because it will depend on how your user displays their date in their preferences and date formats. You also have to consider what happens if one or both fields are blank. You need to consider what if date A is earlier or later than date B. Your answer could be a negative number. Do you display a negative or do you just say the difference is an absolute value?

    Method 2.

    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.