Tech Tip: Date Manipulation in SQL using 4D Function Calls

PRODUCT: 4D | VERSION: 12.5 | PLATFORM: Mac & Win

Published On: April 12, 2013

Date maniputation in 4D's SQL implementation is very easy with the use of 4D Function Calls via the fn keyword in SQL. Here is an approach to completing this task.

First create a method in 4D, in this example we saved the method as SQL_DATE_ADD, then set the method property for 'Available through SQL' to true:

Next add the following contents to the method:

// Example of DATE_ADD method in 4D;
// with ‘available through SQL’ checked in the method properties

C_TEXT($1) // datePart
C_LONGINT($2) // delta value
C_DATE($3) // data to manipulate
C_DATE($0) // returned value
If(Count parameters=3)
  Case of
    : ($1="year")
      $0:=Add to date($3;$2;0;0)
    : ($1="month")
      $0:=Add to date($3;0;$2;0)
    : ($1="day")
      $0:=Add to date($3;0;0;$2)
  End case
End if

Once the above listed method is saved it can be used as in the following examples:

// example of date manipulation in 4D:
// add 20 years to date

ARRAY DATE(mDate;0)
Begin SQL
   SELECT {fn SQL_DATE_ADD('year', 20, Table_1.Field_1) as timestamp}
   from Table_1
   into :mDate;
End SQL

// example of date manipulation in 4D:
// add 20 months to date

ARRAY DATE(mDate;0)
Begin SQL
   SELECT {fn SQL_DATE_ADD('month', 20, Table_1.Field_1) as timestamp}
   from Table_1
   into :mDate;
End SQL

// example of date manipulation in 4D:
// add 20 days to date

ARRAY DATE(mDate;0)
Begin SQL
   SELECT {fn SQL_DATE_ADD('day', 20, Table_1.Field_1) as timestamp}
   from Table_1
   into :mDate;
End SQL

Note: Subtraction is also possible using the same method:

// example of date manipulation in 4D:
// subtract 20 years from date

ARRAY DATE(mDate;0)
Begin SQL
   SELECT {fn SQL_DATE_ADD('year', -20, Table_1.Field_1) as timestamp}
   from Table_1
   into :mDate;
End SQL