Get month end date

Applications Forms
#1

How would I use this to get the month end date? Example if registration_date = 9-15-2017 I would want the due_date to be 10-31-2017.


//Initializing the parameters to be add
$add_days  = 0;  //adding 30 days
$add_months = 1;  //adding nothing to months
$add_years = 0;   //adding nothing to years
{due_date} = sc_date({registration_date}, 'yyyy-mm-dd', '+', $add_days, $add_months, $add_years);
#2

If you is using mysql, you can use mysql functions throuht a Select:

sc_lookup(rs, “select last_day({registration_date}) + interval 1 month”);
{due_date} = {rs[0][0]};

#3

I’m trying to update / insert into another table. I currently have due_date created based on a trigger, but because of that the other table isn’t updating. Since I’ll have many different registration_dates I’m limited to one trigger (mysql) so I’m trying to find another way to get the end of the month. Will the above work on update / insert to another table?

I’m trying something like this:



// SQL statement parameters
$insert_table  = 'testing_table';      // Table name
$insert_fields = array(   // Field list, add as many as needed
     'registration_date' => "'{registration_date}'",
     'due_date' => "LAST_DAY'{registration_date}, INTERVAL 1 MONTH'",
 );

// Insert record
$insert_sql = 'INSERT INTO ' . $insert_table
    . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
    . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';

sc_exec_sql($insert_sql);
#4

@alvagar I’m still not able to get a date in due_date.

#5

It works for me, I tried and worked:

sc_lookup(rs, “select last_day(now()) + interval 1 month”);
$due_date = {rs[0][0]};

$insert_table = ‘my_table’; // Table name
$insert_fields = array( // Field list, add as many as needed
‘id’ => 0,
‘mydate’ => “’$due_date’”,
);

// Insert record
$insert_sql = ‘INSERT INTO ’ . $insert_table
. ’ (’ . implode(’, ‘, array_keys($insert_fields)) . ‘)’
. ’ VALUES (’ . implode(’, ', array_values($insert_fields)) . ‘)’;

sc_exec_sql($insert_sql);

#6

I’m trying with my {registration_date} instead of now(). Using {registration_date} still isn’t working when used on BeforeInsert or AfterInsert events.


sc_lookup(rs, "select last_day({registration_date}) + interval 1 month");
$due_date = {rs[0][0]};

Below does work, but I’d like to use {registration_date} instead of now(). The above doesn’t work.


sc_lookup(rs, "select last_day(now()) + interval 1 month");
$due_date = {rs[0][0]};
#7

I’d tried this in BeforeInsert and AfterInsert. Ideally, I’d like it AfterInsert. Everything works except the due_date which is inserted into ‘real_table’ by a trigger in the mysql database. What the heck am I doing wrong?

 sc_lookup(rs, "select last_day({registration_date}) + interval 1 month");
  $due_date1 = {rs[0][0]};
   
  // SQL statement parameters
  $insert_table  = 'testing_table';      // Table name
  $insert_fields = array(   // Field list, add as many as needed
  'doc_id' => "'{doc_id}'",
       'member_cat' => "'{member_cat}'",
       'registration_date' => "'{registration_date}'",
       'due_date' => "'$due_date1'",
   
   );
   
  // Insert record
  $insert_sql = 'INSERT INTO ' . $insert_table
      . ' ('   . implode(', ', array_keys($insert_fields))   . ')'
      . ' VALUES ('    . implode(', ', array_values($insert_fields)) . ')';
   
  sc_exec_sql($insert_sql);
#8

Finally got it, after I found that I was missing some single quotes!!! Gotta love it!