How does date format autoconversion work

Applications Forms
#1

The form displays fields from two tables. The first table is linked when the app is created. The wanted fields from second table are manually added in Forms Setting->Fields. On_Load the second db is queried and the fields filled with the values found.

Among the fields of the second table is a date field. Except the date field all fields display properly when opening the form,.
With an sc_alert the date field checked in the onLoad event. It contains the date in format ‘YYYY-mm-dd’

The form opended diplays the date field: It contains format letters and no date.

Questions:

  • What format conversion is done when reading date from the db?
  • How to format the date in the OnLoad event that will display properly in the opened form?
  • Is there a better method for a form with data from two tables?
#2

Answer to the questions of sawjer:

  1. The db field can be of type date. For this situation the following explains the underlying principles.

  2. There are two situations where conversion is necessary:

2.1) In an application using the field of type date in the db with a field of type date in scriptcase.
if you set regional setting, do not wonder, why some of the settings are not available. It limits the choice to what seems to be used in the selected region.
Else, there are all the preset formats to choose from.

2.2)Testing the data of the date field will not show the date from the db but the format selected (e.g. DD.MM.YYYY). The field “internal format” needs to be set: YYYY-MM-DD
This seems to be the default internal conversion from the real database internal format (binary), at least in my case of mysql.
The Explanation callable ajacent to the field does not explain this correctly!

2.3 The other situation comes up, when the date is used in a php date calculation, e.g. calculating the time difference between date and today.
In Event->onLoad {date_field} will return the default date format from the db (YYYY-MM-DD).
Calculating the date difference:

//calculate the time difference
$currentDate = date('Y-m-d');
$dateField={date_field};
{differences}=sc_date_dif_2($dateField,"yyyy-mm-dd",$currentDate,"yyyy-mm-dd",1);