Business Rules 301: Date Functions
This blog post is part of a series designed to demystify the process of understanding and writing your own business rules.
Before we begin working with these four date functions, we must establish a baseline understanding of how date formatting can be used. Please remember that all letters used to represent all time formats are case sensitive.
These will likely be the most commonly used nomenclatures used in writing a business rule:
- d — returns a single-digit day of the month: 01/01/17 = 1, 01/02/17 = 2, 01/24/17 = 24
- dd — returns a two-digit day of the month: 01/01/17 = 01, 01/02/17 = 02, 01/24/17 = 24
- ddd — returns the first three letters of the day of the week: Mon, Tue, Wed
- dddd — returns the full name of the day of the week: Monday, Tuesday, Wednesday
- M — returns month of year specified as a single digit (when applicable): Jan = 1, Feb = 2, Dec = 12
- MM — returns the two-digit month of the year: Jan = 01, Feb = 02, Dec = 12
- MMM — returns the first three letters of the month of the year: Jan, Feb, Aug
- MMMM — returns the full name of the month of the year: January, February, August
- yy — returns two digit year: 2017 = 17
- yyyy — returns four digit year: 2017 = 2017
In addition to returning the date format, you also have options to return time of day increments:
- h — returns a single-digit hour of the day (when applicable): 07:05:45 = 7, 10:30:45 = 10
- hh — returns a two-digit hour of the day: 07:05:45 = 07, 12:30:45 = 12
- H — returns the same as ‘h’ except will use single-digit military time notation: 07:05:45 (am) = 7, 01:30:45 (pm) = 13
- HH — returns the same as ‘HH’ except will use two-digit military time notation: 07:05:45 (am) = 07, 01:30:45 (pm) = 13
- m — returns a single-digit minute of the hour: 07:05:45 = 5, 12:30:45 = 30
- mm — returns a two-digit minute of the hour: 07:05:45 = 05, 12:30:45 = 30
- tt — returns the appropriate 12 hour period: AM or PM
Although this is not an exhaustive list, these codes are among the most commonly used for date and time.
GETCURRENTDATE will return the current date in the format you specify.
GETCURRENTDATE(“MMddyyyy”) — Will return two-digit month, two-digit day, and four-digit year. For example, for June 12, 2017, the value returned will be 06122017. On its own this may not look useful, but with a few modifications it can look much more friendly.
If you would like to return the date with a hyphen between the month and day, and a hyphen between the day and the year, use GETCURRENTDATE.
GETCURRENTDATE(“MM-dd-yyyy”) — For a date of June 12, 2017, will return 06-12-2017. The hyphen in this example is interchangeable with most every character. So GETCURRENTDATE(“MM/dd/yyyy”) will return 06/12/2017.
ADDTOCURRENTDATE will add a specified number of days, months or years to today’s current date. The formatting for this rule will first ask for the format you have chosen for your output, then the unit to be measured by and, finally, the number of the unit to be increased.
ADDTOCURRENTDATE(“MM-dd-yyyy”,”d”,30) -— If the current date is June 12, 2017, this command will add 30 days and will, therefore, return a value of 07-12-2017.
To add two months to today’s current date, use the following format. ADDTOCURRENTDATE(“MM-dd-yyyy”,”M”,2) —– If executed on our example date of June 12, 2017, would return 08-12-2017.
As with the GETCURRENTDATE function, you can set the specified formatting of the date to the format that best suits your needs.
In the same vein as ADDTOCURRENTDATE, the function ADDTODATE works similarly, with only one added component.
ADDTODATE will add a specified number of days, months or years to a date you choose. The formatting for this rule will first ask for a date of your choosing (or one found in your inventory), using the format you have chosen for your output, then the unit to be measured by and, finally, the number of the unit to be increased.
ADDTODATE(“02-28-2017”,“MM-dd-yyyy”,”d”,30) — Will return 03-30-2017. The original date specified is February 28, 2017, and the command is requesting 30 days be added to that date.
One important caveat is that the format of the supplied date (the first field required) should be in the format of month, day and year. This means that if you have your date stored in your inventory in another way (such as day, month, year), you may need to use a function like GETPART to rearrange the date before using it in the ADDTODATE function.
GETDAYSBETWEEN does exactly what it sounds like. It will return the number of days between two specified dates. Three criteria are needed for the formatting of this rule: first, the starting date; secondly, the ending date; and, finally, the format the both dates are in.
GETDAYSBETWEEN(“02-28-2017”,”03-25-2017”,”MM-dd-yyyy”) — Will return 25.
Now that we’ve discussed and defined each rule, you should know that you can also nest these rules to achieve the desired output of your choice.
Keep these points in mind when using rules that will generate date and time:
- Using the proper formatting is critical.
- By default, the date functions will return an EST designation.
These rules only run during two times: when information is entering ChannelAdvisor (if the rules are applied to inbound data), or when information is included on a template associated to a marketplace to which the rules apply. This is especially important if you intend to alter pricing information based on the time of day or day of the week. These rules will not automatically update unless the data is fed through a template, or a change is made to the SKU or the template being pushed.
We’ve reached the end of this business rules blog series! If you want to learn more about business rules, feel free to explore more about business rules on our SSC or check out the other posts in this series. If you’re struggling with a rule, you can always open a case with support to assist you.