Calculate no of months

SOLVED

Hi

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?

Top Replies

  • Hi !

    You're right! While the code works in the console itself, it tends to spark an error when executed onscreen.

    Since there's a bit too much to type to explain the "why" behind the code not working…

Parents Reply
  • 0 in reply to SIQ

    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. 

Children
  • 0 in reply to Matthew Shaw

    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

  • 0 in reply to SIQ

    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.