The New Labels dialog is reworked to include a field combo for an existing drawing with a special '(pattern)' choice. Selecting a field, will create labels with the text taken from that field (this is the simplest scenario which we want to keep as simple as possible). Selecting '(pattern)' allows editing the text pattern directly. Switching a drawing tries to select a field which makes the most sense to display in labels, the priority order is: a text field > a non-text field that does not have a unique index built on it (to deprioritize fields like MFD_ID or SHAPEID) > a non-text field that has a unique index built on it.
The Components tab of the Info pane no longer shows the 'Text' field for labels. (Labels are now using text patterns.)'
The Info pane automatically determines fields referenced in the text pattern for a labels component, and shows a distinctive icon near each referenced field in the Values tab. Starting to add a new label or starting to edit an existing label also automatically puts the cursor onto the value of the first such field.
New query function: StringFormatDateTime. The function takes a datetime value to format, a format pattern, a language, and returns a formatted string. The language can be an empty string (neutral) or a language code like 'en-US' or 'de-DE'. The format pattern can be either a single letter that defines the whole format, or a combination of the format parts.
(Format patterns for all new functions are roughly compatible with .NET even though the functions themselves are implemented using native Windows calls. We are not using .NET directly because there are cases where this would waste too much resources. We are not currently using ICU, because it is only available on Windows 10 and we need to have reasonable fallback behavior for non-Windows 10 systems anyway. In the future we will likely allow formatting calls to use ICU, if it is available.)
Single-letter formats are (example value is '25 March 2020 13:45:00', example language is 'en-US'):
- d = short date (3/25/2020)
- D = long date (Wednesday, March 25, 2020)
- f = long date + short time (Wednesday, March 25, 2020 1:45 PM)
- F = long date + long time (Wednesday, March 25, 2020 1:45:00 PM)
- g = short date + short time (3/25/2020 1:45 PM)
- G = short date + long time (3/25/2020 1:45:00 PM)
- m = short month and day (25 Mar)
- M = long month and day (25 March)
- r/R = RFC1123, the language does not matter and is always neutral (Wed, 25 Mar 2020 13:45:00 GMT)
- s = sortable, the language does not matter (2020-03-25T13:45:00)
- t = short time (1:45 PM)
- T = long time (1:45:00 PM)
- u = universal sortable, the language does not matter (2020-03-25 13:45:00Z)
- y/Y = year and month (March 2020)
An empty format string is interpreted as 'G' = short date + long time.
If the format string differs from any of the single-letter formats and is not empty, it is interpreted as a combination of the following format parts:
- d/dd = day, two-letter variants of this and other parts add a leading zero for one-digit values (25)
- ddd = short week day (Wed)
- dddd = long week day (Wednesday)
- g/gg = era (A.D.)
- h/hh = hour on a 12-hour scale (1)
- H/HH = hour on a 24-hour scale (13)
- m/mm = minute (45)
- M/MM = month as a number (3)
- MMM = short month (Mar)
- MMMM = long month (March)
- s/ss = second (0)
- t/tt = time marker (PM)
- y/yy = short year (20)
- yyy/yyyy/yyyyy = long year, accepts up to five letters because some languages allow up to five (2020)
Any character or any character sequence different from those above (eg, 'x' or 'hhh') is copied without any changes.
Example pattern: d-MMM-yy h:mm (25-Mar-20 1:45)
The default conversion of a datetime value to a string now uses leading zeros for month, day and hour to coincide with the output of the empty format for the neutral language.
New query function: StringFormatDurationDays. The function takes a number of days to format, a format pattern, a language, and returns a formatted string. The format pattern is a combination of the format parts:
- d = days
- f/ff/fff/ffff/... (up to 9x) = fractions of a second
- h/hh/H/HH = hours
- m/mm = minutes
- s/ss = seconds
The format parts are required to follow from bigger to smaller.
An empty format string is interpreted as 's' = seconds.
Example: ? StringFormatDurationDays(1.133, '', '') -- 97891 (seconds)
Example: ? StringFormatDurationDays(1.133, 'd:hh', '') -- 1:03
Example: ? StringFormatDurationDays(1.133, 'h:mm:ss', '') -- 27:11:31
New query function: StringFormatDurationSeconds. Same as StringFormatDurationDays, but the value to format is in seconds.
New query function: StringFormatNumber. The function takes a numeric value to format, a format pattern, a language, and returns a formatted string. The format pattern consists of a single-letter format and an optional integer number specifying the desired number of digits or decimal digits, depending on the format.
Single-letter formats are (example value is 123456.7890123, example language is 'en-US'):
- c/C = currency, with the optional number of decimals ($123,456.79)
- d/D = decimal integer, with the optional total number of digits and optional leading zero, the language does not matter (123457, or, for 'd08': 00123457)
- e/E = exponential, with the optional number of decimals, the default number of decimals is 6 regardless of the language (1.234568e+05)
- f/F = fractional, with the optional number of decimals (123456.79)
- n/N = fractional + digit groups, with the optional number of decimals (123,456.79)
- x/X = hexadecimal integer, with the optional total number of digits and optional leading zero, the language does not matter (1e241)
If the format string is empty or invalid, the number is formatted as the shortest form that preserves all significant digits, the language does not matter (123456.7890123). The case of 'e' / 'E' or 'x' / 'X' specifies the case of the exponent character or the case of the hexadecimal letters.
New query function: StringFormatVector. The function takes a vector value to format, a format pattern, a language, and returns a formatted string. The format pattern is the same as for StringFormatNumber. The formatted string consists of strings for the individual vector values separated using a list separator for the specified language, and enclosed in square brackets (example: [ 2.34, 5.67 ]).
New query function: StringFormatUuid. The function takes an UUID value to format, a format pattern, and returns a formatted string. The format can be either 'x' or 'X', the case controls the case of the hexadecimal letters. The default is 'x'.
New query function: StringFormatBoolean. The function takes a boolean value to format, a format pattern, and returns a formatted string. The format can be either 'b' / 'B' (output TRUE as '1' and FALSE as '0') or 't' / 'T' ('true' / 'false' with the case controlled by the letter). The default is 't'.
For all format functions, using an empty format string and an empty language string produces the same string as the default conversion to a string: StringFormatXxx(..., '', '') produces the same result as CAST (... AS NVARCHAR).
New query function: DateTimeCurrent. The function returns the current date.
New query functions: DateTimeCurrentRef and UuidMakeNewRef. Both functions take a single argument of an arbitrary type. DateTimeCurrentRef then returns the current date. UuidMakeNewRef generates and returns a new UUID. Neither function uses the value of the argument. The purpose of having an argument is to tell the query engine that the it might not cache the result returned by the function between calls where the argument value might change. This is useful in multiple ways.
For example, this allows forcing a new UUID value for each record in a SELECT:
SELECT [name], UuidMakeNew() AS [uuid] FROM [cities table];
...all records are going to have the same UUID. Because the query engine sees that the call to UuidMakeNew() is exactly the same for each record, so it is only going to call it once, cache the result, then copy the result into each record.
SELECT [name], UuidMakeNewRef([name]) AS [uuid] FROM [cities table];
...each record is going to have a new UUID generated. Because the calls to UuidMakeNewRef(...) are different between records, the query engine sees that (sighs) and dutifully calls the function anew for each record.
For another example, this allows creating a computed field with the current date which would update itself after you change some of the fields.
ALTER TABLE [cities table] (
ADD [last_edited] DATETIME AS [[
DateTimeCurrentRef([name] & [state] & CAST([population] AS NVARCHAR))
...takes a table named 'cities table' and adds a computed datetime field named 'last_edited'. Adding a field populates it with the current date. If you then pick some record and change the value of one of the fields mentioned in the expression: 'name', 'state' or 'population', the computed field in that record will be recomputed and set to the now-current date.
End of list.