Home : Date Literal for Math Toolbox Functions
Q10944 - INFO: Date Literal for Math Toolbox Functions

Since dates must be supplied in the advanced math toolbox function arguments, Math Toolbox supports several date manipulation funcitons.  The "date literal" functionality has been expanded.  In addition to all the existing features, you can now specify offsets to the current date being calculated.  In the day, month or year fields, if you specify an unsigned number then that number is used as the absolute day, month or year. If you specify a number with a + or - sign in front of it, then it indicates the number of days, months or years to offset from the current value.  You can use any combination of signed or unsigned number for each field:

D Current day being calculated
#MM/DD/YY Current date being calculated
#MM/LL Last day of the current month being calculated
#1/1 January 1 of the current year being calculated
#+1/1 The first of next month from the date being calculated
#+1/LL The last day of next month from the current date being caluclated
#1/1/+1 January 1 of next year from the date being calculated being calculated
#-1/LL The last day of the previous month from the date being calculated
#MM/-7 Date seven days ago from the current date being calculated
BOM Beginning of the current Month being calculated
EOM End of the current month (i.e. last day of month)
BOQ Beginning of the current Quarter being calculated
EOQ End of the current Quarter being calculated
EOD End of current day being calculated (23:59:59)
#MM/DD/YY/HH/NN 

Current date hour (HH) and minutes (NN) of current date/slot being calculated.  Used primarily when calculating daily detail results

WEEKSTART(N) Start Date of the week definition N for the current date/slot being calculated.
WEEKEND(N) End Date of the week definition N. (The end date is only defined when the current date being calculated is the date on which Week definition N would normally bring, otherwise WeekEnd is NoDate)

NOTE: If the current date being calculated is March 25, 2003, then #-1/DD means February 25, 2003.  If the current date being calculated is March 30, 2003, then #-1/DD means February 28, 2003.  (If you actually wanted thirty days from the first of the previous month, you would use #-1/1 +30)

EXAMPLE #1: Calculate the average of an hourly variable from the 1st of the month thru 1PM of each day

GETAVG(C1,BOM TO #MM/DD/YY/13/NN,BYHOUR)

When calculating Jan 2nd,2015 into a daily variable this would calc from 1/1/2015 00:00 thru the 1/2/2015 13:00 Slot (i.e. data for the 1PM hour will be included in the calc). 

 

EXAMPLE #2: Calculate the 7 day moving avearage from an hourly variable into a daily calculation. 

GETAVG(C1,#MM/-6 TO EOD, BYHOUR)

When calculating Jan 7th, 2015 this calc would include data from Jan 1st 00:00 thru the last hourly slot of Jan 7th, 2015 (i.e. data for Jan 7th, 11PM slot will be included).

EXAMPLE #3: Calculate the 12 Month Average on the last day of the month

GETAVG(C41,#-11/1 TO TodayIF(D=#MM/LL))

 

Other Date Functions:

NoDate Equivalent to a blank date value
Month (Date) Month number, 1-12, of the date (i.e., Month(#5/1)=5)
Day (Date) Day of the month, 1-31 (i.e., Date(#1/LL) = 31)
Year (Date) Year of the provided date (i.e., Year(#1/1/2008) = 2008
Weekday (Date)

Weekday number of Date (i.e., Weekday(#1/1/2001) = 2 (Monday)

    1. = Sunday
    2. = Monday
    3. = Tuesday
    4. = Wednesday
    5. = Thursday
    6. = Friday
    7. = Saturday
TodayIF(condition) Returns Current Date being calculated when condition is True, else NoDate when false
short form for the expression IF(condition, D, Nodate)

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 4/17/2008 2:50 PM.
Last Modified on 2/14/2022 5:43 PM.
Last Modified by Scott Dorner.
Article has been viewed 6113 times.
Rated 5 out of 10 based on 6 votes.
Print Article
Email Article