Hi, Readers.
Today I would like to share another tip about Business Central. During the development, there may be situations where you need to get the Start Date and End Date (Period) of the week/month/quarter/year (Includes last and next) from a given date. How can we do this quickly? In this post, I will briefly summarize and hopefully give you some help.
This time we use CalcDate Method.
CalcDate Method: Calculates a new date that is based on a date expression and a reference date.
Syntax:
NewDate := System.CalcDate(DateExpression: String [, Date: Date])
Parameters:
DateExpression
Type: Text
The date expression can be any length. The string is interpreted from left to right with one subexpression at a time. The following rules describe the valid syntax of date expressions:
- DateExpression = [<Subexpression>][<Subexpression>][<Subexpression>]
- <Subexpression> = [<Sign>] <Term>- <Sign> = + | –
- <Term> = <Number><Unit> | <Unit><Number> | <Prefix><Unit>
- <Number> = Positive integer
- <Unit> = D | WD | W | M | Q | Y (D=day, WD=weekday, W=week, M=month, Q=quarter, Y=year)
- <Prefix> = C (C=current) These production rules show that date expressions consist of zero, one, two, or three subexpressions. Each subexpression consists of an optional sign and a term. The following are some typical examples of terms:
- 30D (30 days; corresponds to <Number><Unit>)
- WD2 (weekday number 2; corresponds to <Unit><Number>)
- CW (current week; corresponds to <Prefix><Unit>) The internal calendar starts on Monday and ends on Sunday. This means that Monday is weekday 1 and Sunday is weekday 7. A run-time error occurs if the syntax of DateExpression is incorrect.
Let’s see more details. For simple testing, I used the Posting Date on the Sales Order as the Given Date.
This week, last week and next week
Key Code:
//This Week
ThisWeekStartDate := CalcDate('<-CW>', GivenDate);
ThisWeekEndDate := CalcDate('<CW>', GivenDate);
//Last Week
LastWeekStartDate := CalcDate('<-CW-1W>', GivenDate);
LastWeekEndDate := CalcDate('<CW-1W>', GivenDate);
//Next Week
NextWeekStartDate := CalcDate('<-CW+1W>', GivenDate);
NextWeekEndDate := CalcDate('<CW+1W>', GivenDate);This month, last month and next month
Key Code:
//This Month
ThisMonthStartDate := CalcDate('<-CM>', GivenDate);
ThisMonthEndDate := CalcDate('<CM>', GivenDate);
//Last Month
LastMonthStartDate := CalcDate('<-CM-1M>', GivenDate);
LastMonthEndDate := CalcDate('<CM>', LastMonthStartDate);
//Next Month
NextMonthStartDate := CalcDate('<-CM+1M>', GivenDate);
NextMonthEndDate := CalcDate('<CM>', NextMonthStartDate);This quarter, last quarter and next quarter
Key Code:
//This Quarter
ThisQuarterStartDate := CalcDate('<-CQ>', GivenDate);
ThisQuarterEndDate := CalcDate('<CQ>', GivenDate);
//Last Quarter
LastQuarterStartDate := CalcDate('<-CQ-1Q>', GivenDate);
LastQuarterEndDate := CalcDate('<CQ>', LastQuarterStartDate);
//Next Quarter
NextQuarterStartDate := CalcDate('<-CQ+1Q>', GivenDate);
NextQuarterEndDate := CalcDate('<CQ>', NextQuarterStartDate);This year, last year and next year
Key Code:
//This Year
ThisYearStartDate := CalcDate('<-CY>', GivenDate);
ThisYearEndDate := CalcDate('<CY>', GivenDate);
//Last Year
LastYearStartDate := CalcDate('<-CY-1Y>', GivenDate);
LastYearEndDate := CalcDate('<CY-1Y>', GivenDate);
//Next Year
NextYearStartDate := CalcDate('<-CY+1Y>', GivenDate);
NextYearEndDate := CalcDate('<CY+1Y>', GivenDate);Test video:
Source code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)
pageextension 50204 SalesOrderExt extends "Sales Order"
{
layout
{
modify("Posting Date")
{
trigger OnAfterValidate()
begin
GetWeekPeriodFromGivenDate(Rec."Posting Date");
GetMonthPeriodFromGivenDate(Rec."Posting Date");
GetQuarterPeriodFromGivenDate(Rec."Posting Date");
GetYearPeriodFromGivenDate(Rec."Posting Date");
end;
}
}
var
WeekMsg: Label 'This week: %1 ~ %2\Last week: %3 ~ %4\Next week: %5 ~ %6';
MonthMsg: Label 'This month: %1 ~ %2\Last month: %3 ~ %4\Next month: %5 ~ %6';
QuarterMsg: Label 'This quarter: %1 ~ %2\Last quarter: %3 ~ %4\Next quarter: %5 ~ %6';
YearMsg: Label 'This year: %1 ~ %2\Last year: %3 ~ %4\Next year: %5 ~ %6';
local procedure GetWeekPeriodFromGivenDate(GivenDate: Date)
var
ThisWeekStartDate: Date;
ThisWeekEndDate: Date;
LastWeekStartDate: Date;
LastWeekEndDate: Date;
NextWeekStartDate: Date;
NextWeekEndDate: Date;
begin
//This Week
ThisWeekStartDate := CalcDate('<-CW>', GivenDate);
ThisWeekEndDate := CalcDate('<CW>', GivenDate);
//Last Week
LastWeekStartDate := CalcDate('<-CW-1W>', GivenDate);
LastWeekEndDate := CalcDate('<CW-1W>', GivenDate);
//Next Week
NextWeekStartDate := CalcDate('<-CW+1W>', GivenDate);
NextWeekEndDate := CalcDate('<CW+1W>', GivenDate);
Message(WeekMsg, ThisWeekStartDate, ThisWeekEndDate, LastWeekStartDate, LastWeekEndDate, NextWeekStartDate, NextWeekEndDate);
end;
local procedure GetMonthPeriodFromGivenDate(GivenDate: Date)
var
ThisMonthStartDate: Date;
ThisMonthEndDate: Date;
LastMonthStartDate: Date;
LastMonthEndDate: Date;
NextMonthStartDate: Date;
NextMonthEndDate: Date;
begin
//This Month
ThisMonthStartDate := CalcDate('<-CM>', GivenDate);
ThisMonthEndDate := CalcDate('<CM>', GivenDate);
//Last Month
LastMonthStartDate := CalcDate('<-CM-1M>', GivenDate);
LastMonthEndDate := CalcDate('<CM>', LastMonthStartDate);
//Next Month
NextMonthStartDate := CalcDate('<-CM+1M>', GivenDate);
NextMonthEndDate := CalcDate('<CM>', NextMonthStartDate);
Message(MonthMsg, ThisMonthStartDate, ThisMonthEndDate, LastMonthStartDate, LastMonthEndDate, NextMonthStartDate, NextMonthEndDate);
end;
local procedure GetQuarterPeriodFromGivenDate(GivenDate: Date)
var
ThisQuarterStartDate: Date;
ThisQuarterEndDate: Date;
LastQuarterStartDate: Date;
LastQuarterEndDate: Date;
NextQuarterStartDate: Date;
NextQuarterEndDate: Date;
begin
//This Quarter
ThisQuarterStartDate := CalcDate('<-CQ>', GivenDate);
ThisQuarterEndDate := CalcDate('<CQ>', GivenDate);
//Last Quarter
LastQuarterStartDate := CalcDate('<-CQ-1Q>', GivenDate);
LastQuarterEndDate := CalcDate('<CQ>', LastQuarterStartDate);
//Next Quarter
NextQuarterStartDate := CalcDate('<-CQ+1Q>', GivenDate);
NextQuarterEndDate := CalcDate('<CQ>', NextQuarterStartDate);
Message(QuarterMsg, ThisQuarterStartDate, ThisQuarterEndDate, LastQuarterStartDate, LastQuarterEndDate, NextQuarterStartDate, NextQuarterEndDate);
end;
local procedure GetYearPeriodFromGivenDate(GivenDate: Date)
var
ThisYearStartDate: Date;
ThisYearEndDate: Date;
LastYearStartDate: Date;
LastYearEndDate: Date;
NextYearStartDate: Date;
NextYearEndDate: Date;
begin
//This Year
ThisYearStartDate := CalcDate('<-CY>', GivenDate);
ThisYearEndDate := CalcDate('<CY>', GivenDate);
//Last Year
LastYearStartDate := CalcDate('<-CY-1Y>', GivenDate);
LastYearEndDate := CalcDate('<CY-1Y>', GivenDate);
//Next Year
NextYearStartDate := CalcDate('<-CY+1Y>', GivenDate);
NextYearEndDate := CalcDate('<CY+1Y>', GivenDate);
Message(YearMsg, ThisYearStartDate, ThisYearEndDate, LastYearStartDate, LastYearEndDate, NextYearStartDate, NextYearEndDate);
end;
}PS: Dynamics 365 Business Central: About complex Due Date Calculations
END
Hope this will help.
Thanks for reading.
ZHU
コメント