Use system date format in forms

Problem

Problem 1

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.

Problem 2

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.

Solution

Solution 1

Using Text object on your form

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).

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

Using Edit Field (Date picker) on your form

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.

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

  1. In Form Properties > Additional settings select Python scripting language.

  2. Add a variable with the name SystemDateFormat to your form.

  3. Add a variable with the name SelectedDate to your form.

  4. Add Edit field object with connected data source SelectedDate. Change the edit field name to DatePicker (in Properties > General > Name).

  5. 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 

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

Solution 2

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

  • In Loftware 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.

  • When storing date to SQL database use Date or Datetime2 data types instead of nvarchar.

  • When reading date from the database, use the FORMAT function:

    SELECT FORMAT ([Date], :SystemDateFormat)
FROM [dbo].[Order]

  • Adjust UTC time offset for the system time zone:

    1. Get offset:

      from System import TimeZoneInfo, DateTime

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

    2. Use offset in a query:

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