Convert date string containing a month text value to a date value

Assuming you want to enter date values in the RTC client like 15-MAR-14. that won’t work. date values are only accepted with syntax like 15.03, 15.03.14, etc. But it can be that users prefer the above syntax. so then you can use a text field in your page and convert internally the value into a date value. Following function converts that kind of date string to a date value.

// local variables
// dateString | Text
// dateValue  | Date
dateString := '15-MAR-14';
dateValue := ConvertDateString(dateString);
MESSAGE(FORMAT(dateValue,0,'<day,2>.<month,2>.<year4>'));

ConvertDateString(dateString : Text[9]) : Date
// local variables
// dateString | Text
// dayValue   | Integer
// months     | Text
// monthValue | Integer
// yearValue  | Integer
// dateValue  | Date
// mPos       | Integer
// monthText  | Text

if strpos(dateString,'-') > 0 then begin
  dateString := CONVERTSTR(dateString,'-',',');
  EVALUATE(dayValue,SELECTSTR(1,dateString));
  months := 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec';
  monthText := lowercase(SELECTSTR(2,dateString));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(months,monthText);
  if mPos >0 then begin
    monthValue := (mPos + 3) / 4;
    EVALUATE(yearValue,SELECTSTR(3,dateString));
    IF yearValue > 14 THEN
      yearValue := yearValue + 1900
    ELSE
      yearValue := yearValue + 2000;
    dateValue := DMY2DATE(dayValue,monthValue,yearValue);
    exit(dateValue);
  end else
    error('No valid month given: ' + monthText);
end;
exit(0D);

to use the standard functionality of date validation and conversion it’s needed to change function MakeDateText in codeunit 1:

MakeDateText(VAR DateText : Text[250]) : Integer
// additional local variables
// mPos | Integer
// monthText | Text | 10
// monthValue | Integer
// Text Constant: Text022 | jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec

Position := 1;
Length := STRLEN(DateText);
ReadCharacter(' ',DateText,Position,Length);
 
// begin changes
IF STRPOS(DateText,'-') > 0 THEN BEGIN
  DateText := CONVERTSTR(DateText,'-',',');
  monthText := lowercase(SELECTSTR(2,DateText));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(Text022,monthText);
  IF (mPos > 0) THEN BEGIN
    monthValue := (mPos + 3) / 4;
    // optional: add a leading 0, if needed; simple use format(monthValue) should also work
    monthText := PADSTR('',2 - STRLEN(FORMAT(monthValue)),'0') + FORMAT(monthValue);
    DateText := SELECTSTR(1,DateText) + '-' + monthText + '-' + SELECTSTR(3,DateText);
  END ELSE
    error('No valid month given: ' + monthText);
END;
// end changes
 
IF NOT FindText(PartOfText,DateText,Position,Length) THEN
...

cheers

Advertisements

Date Functions

Date functions are quite often used and needed. Here are some samples:

1. Calculate the 1st of current month:

   EndDate := CALCDATE('-CM');
   

2. Calculate last day of current month:

   EndDate := CALCDATE('CM');
   

3. Calculate the end date of a defined period (e.g.3 months) starting with the 1st of the current month; get the last day of the end month:

   EndDate := CALCDATE('-CM+3M-1D');
   EndDate := CALCDATE('CM+2M');
   

4. Get start and end date of a period with given day values and variable month value:

   startDay, endDay, month, prevMonth, currYear | integer values
   month := 10  // sample value
   if month > 1 then prevMonth := month - 1 else prevMonth := 12;
   currYear := DATE2DMY(TODAY,3);  // get current year
   startDate := DMY2DATE(startDay, month - 1, currYear);
   endDate := DMY2DATE(endDay, month, currYear);
   

Be aware that the calc formulars are language specific. For german language e.g. use LM instead of CM, 1T instead of 1D.