1. NiceLabel Help Center
  2. Knowledge Base
  3. How To

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

13_date_format_2.png

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

    14_date_format_3.png

  • 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]