[en] Use system date format in forms

[en] Problem

[en] Problem 1

[en] You created a form that customers will use in other countries where default date formats are different than in your country. Your form should display a country-specific date format.

[en] Problem 2

[en] You would like to store your country-specific date format in SQL database. Customers in other countries would like to retrieve the date from the database in their country-specific date format.

[en] Solution

[en] Solution 1

[en] Using Text object on your form

[en] Create a prompt variable with Data type set to Date and leave the Input format option empty (delete the current value in the edit field).

[en] When you put the text object with your variable on your form, the current date will display in a local regional format.

[en] Using Edit Field (Date picker) on your form

[en] With the edit field, the above solution creates a string with the regional current date and current time, for example, 22/05/2022 00:00:00.

[en] To create only a date string, add to your form:

[en] In Form Properties > Additional settings select Python scripting language.
[en] Add a variable with the name SystemDateFormat to your form.
[en] Add a variable with the name SelectedDate to your form.
[en] Add Edit field object with connected data source SelectedDate. Change the edit field name to DatePicker (in Properties > General > Name).

[en] Go to Form Properties > Events > On Form Load section and click Actions. Add Execute Script action with the following script:

from System.Globalization import CultureInfo

SystemDateFormat.Value = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern 

from System.Windows import Application, LogicalTreeHelper

curApp = Application.Current;
mainWindow = curApp.Windows[len(curApp.Windows) - 1]
LogicalTreeHelper.FindLogicalNode(mainWindow, "EP_DatePicker").Content.Mask = SystemDateFormat.Value

[en] When running your form, you can select a date from the date picker edit field, and the local system date will generate.

[en] Solution 2

[en] To present the date from the SQL database in a country-specific format, do the following:

[en] In NiceLabel create a prompt variable with Data type set to Date and leave the Input format option empty (delete the current value in the edit field). Set the Output format to yyyy-MM-dd.
[en] When storing date to SQL database use Date or Datetime2 data types instead of nvarchar.
[en] When reading date from the database, use the FORMAT function:
```
SELECT FORMAT ([Date], :SystemDateFormat)
FROM [dbo].[Order]
```

[en] Adjust UTC time offset for the system time zone:

[en] Get offset:

from System import TimeZoneInfo, DateTime

TimeZoneOffset.Value = str(TimeZoneInfo.Local.GetUtcOffset(DateTime.UtcNow).Hours)

[en] Use offset in a query:

SELECT dateadd(hour, cast(:TimeZoneOffset as int), [CreatedOnUTC]) as CreatedOn
FROM [dbo].[PrintingLogStream]