{"id":987,"date":"2011-11-10T19:57:06","date_gmt":"2011-11-10T19:57:06","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/techblog\/?p=987"},"modified":"2023-05-12T13:23:48","modified_gmt":"2023-05-12T19:23:48","slug":"using-true-dates-in-external-objects","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/techblog\/?ht_kb=using-true-dates-in-external-objects","title":{"rendered":"Using True Dates in External Objects"},"content":{"rendered":"

It is well known that fields that are formatted as true dates can be easily compared and manipulated.\u00a0 For instance, getting days between two dates is easy with the DAYS() function.\u00a0 Also, it is easy to add or subtract a certain period of time with SQL.\u00a0 More on date functions within SQL can be found here<\/a>.<\/p>\n

However, when passing a date to an external object for some comparison or other function, one must be sure to follow these steps in order to successfully pass a date field to the external object.<\/p>\n

    \n
  1. First, we must create the external SQL object.\u00a0 For more on this, please see this document<\/a>.\u00a0 The most important part of this, is to add the parameter as a char(10).\u00a0 Then, declare a variable of type date.\u00a0 Finally, set your new variable of type date to the parameter brought in of type char(10) (see code below).<\/li>\n
  2. Now, you can use this new date variable to perform any comparisons or manipulations needed in your function.<\/li>\n
  3. After creating this procedure, open m-Power and create a new external object.\u00a0 Fill out the appropriate fields and register your parameters, being sure to set your date parameter as length of 10.<\/li>\n
  4. Register the external object to your app as normal and compile.<\/li>\n<\/ol>\n

    Now, at run time, any true dates will be submitted and compared as normal.<\/p>\n

    Here is some sample code to help illustrate the CREATE PROCEDURE explained above:<\/h3>\n

    CREATE PROCEDURE MRCWORKLIB.TEST
    \n(INOUT i_error CHAR(99),
    \nINOUT i_date CHAR(10))
    \nLANGUAGE SQL
    \nIS DETERMINISTIC
    \nMODIFIES SQL DATA
    \nBEGIN
    \nDECLARE TRUDATE DATE;
    \nSET TRUDATE = DATE(i_date);<\/p>\n

    CASE
    \nWHEN something happens<\/em> THEN
    \nset i_error = ‘Some error.’;
    \nELSE
    \nset i_error = ”;
    \nEND CASE;
    \nEND<\/p>\n","protected":false},"excerpt":{"rendered":"

    It is well known that fields that are formatted as true dates can be easily compared and manipulated.\u00a0 For instance, getting days between two dates is easy with the DAYS() function.\u00a0 Also, it is easy to add or subtract a certain period of time with SQL.\u00a0 More on date functions…<\/p>\n","protected":false},"author":3,"comment_status":"closed","ping_status":"open","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[],"ht-kb-tag":[],"class_list":["post-987","ht_kb","type-ht_kb","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/987","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=987"}],"version-history":[{"count":9,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/987\/revisions"}],"predecessor-version":[{"id":11730,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=\/wp\/v2\/ht-kb\/987\/revisions\/11730"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=987"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-category&post=987"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/techblog\/index.php?rest_route=%2Fwp%2Fv2%2Fht-kb-tag&post=987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}