2,155
edits
Changes
no edit summary
* '''Date part''': Return a part of a date.
Syntax: ''DATE_PART(<expression_date>;<constant>)''. Need two parameters, the first is the reference date and the second the constant representing the desired part date: 0, 1, 2, 3 or 5 (see above constants list).
'''Examples''': DATE_PART("Order Date";0) - returns the year part of the "Order Date";
* '''First day of''': Return the first day of a grouping. This function have two variations:
** '''First''':
Syntax: ''FIRST_DAY_OF(<constant_grouping>;<expression_date>)''. Need two parameters, the first is the constant grouping in which you want to obtain the first date: 0, 1, 2, 3 or 5 (see constants list) and the second is the anchor base date. '''Examples''': FIRST_DAY_OF(0;DAY_FROM_TODAY(0)) - first day of year (0) from today (DAY_FROM_TODAY(0)), FIRST_DAY_OF(1;"Order Date") - first day of month (1) based on the "Order Data";
** '''Second''':
Syntax: ''FIRST_DAY_OF(<constant_grouping>;<constant_modify_date>;<modify_date_value>)''. Need three parameters, the first is the constant grouping in which you want to obtain the first date: 0, 1, 2, 3 or 5 (see constants list), the second is the modifier of the base date from today: 0, 1, 2, 3 or 5 (see constants list) and the third is the amount to change the modifier.
'''Examples''': FIRST_DAY_OF(1;0;-1) - first day of the current month (1) from last year (0;-1);
* '''Last day of''': Return the last day of a grouping. This function have two variations:
** '''First''':
Syntax: ''LAST_DAY_OF(<constant_grouping>;<expression_date>)''. Need two parameters, the first is the constant grouping in which you want to obtain the last date: 0, 1, 2, 3 or 5 (see constants list) and the second is the anchor base date. '''Examples''': LAST_DAY_OF(0;DAY_FROM_TODAY(0)) - last day of year (0) from today (DAY_FROM_TODAY(0)), LAST_DAY_OF(1;"Order Date") - last day of month (1) based on the "Order Data";
** '''Second''':
Syntax: ''LAST_DAY_OF(<constant_grouping>;<constant_modify_date>;<modify_date_value>)''. Need three parameters, the first is the constant grouping in which you want to obtain the last date: 0, 1, 2, 3 or 5 (see constants list), the second is the modifier of the base date from today: 0, 1, 2, 3 or 5 (see constants list) and the third is the amount to change the modifier.
'''Examples''': LAST_DAY_OF(1;0;-1) - last day of the current month (1) from last year (0;-1);
* '''New date''': Create an object from a day, month and year.
Syntax: ''NEW_DATE(<expression_day>;<expression_month>;<expression_year>)''. Need three parameters, the first is a valid day number (1 to 31), the second for a month (1 to 12) and the third is a year. The date is created from year to day, therefore, the day must be valid for the month and the year (considering 29/02, year must be bissextile). '''Examples''': NEW_DATE(1;4;DATE_PART("Order Date";0)) - create the first day of april from the year determined by the current row "Order Date" column;
* '''Num of days''': Return the number of days between two dates.
Syntax: ''NUM_OF_DAYS(<expression_date1>;<expression_date2>)''. Need two date data type parameters.
'''Examples''': NUM_OF_DAYS(FIRST_DAY_OF(0;"Order Date");"Order Date") - returns the number of days between the first day of year based on the "Order Date" column to the current day of the "Order Date" column, NUM_OF_DAYS(FIRST_DAY_OF(1;"Pay Date"); LAST_DAY_OF(1;"Pay Date")) - returns the number of days of the month of the "Pay Date" column;
* '''Num of months''': Return the number of months between two dates.
Syntax: ''NUM_OF_MONTHS(<expression_date1>;<expression_date2>)''. Need two date data type parameters.
'''Examples''': NUM_OF_MONTHS(FIRST_DAY_OF(0;"Date");"Date") - Return the number of months between the first day of the year of the "Date" column and the current month of the "Date" column;
* '''Previous date''': Return the closest exact previous date given a day and month.
Syntax: ''PREVIOUS_DATE(<expression_day>;<expression_month>)''. Need two parameters, the first is the day of month and the second the month. '''Examples''': PREVIOUS_DATE(1;4) - return the closest ''01/04'', in other words, until "01/04/current year", returns "01/04/last year", after that returns "01/04/current year".