Kexi/Plugins/Queries/SQL Constants

From KDE Community Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Common

Common notes:

  • {whitespace} means any non-empty combination of tabulators and spaces; new line character (\n) is not supported

Date constants

(since 3.1.1, Bug #393094)

Grammar - two versions:

  • ISO format (KEXI default, recommended)
#Syyyy-mm-dd#
  • US format (for compatibility, based on MSA default [1])
#mm/dd/Syyyy#
  • S means "-" or "+" or "", see below

Notes:

  • Both formats are not dependent on regional settings on the OS, they are always interpreted the same way
  • One or two digits are required for month and day, e.g. #2018-4-15# and #2018-04-1# are both valid dates
  • Years encoding is based on extended ISO 8601:
    • At least four digits are required for year, e.g. #18-4-15# is invalid date
    • If there are more than four digits, "+" prefix is required, e.g. #+123456-12-15#
    • year >= 0001 means year of Anno Domini, limit is std::numeric_limits<int>::max()
    • by convention 1 BC is labelled +0000 (unlike in QDate where 0 is invalid year)
    • by convention 2 BC is labelled -0001, 3 BC is labelled -0002, etc. (unlike in QDate where 1 means 1 BC), limit is std::numeric_limits<quint64>::min()
  • MSA's format such as #24 Dec 2014# is not supported.
  • Detailed grammar of VBA for reference: https://msdn.microsoft.com/en-us/library/dn528865.aspx

Time constants

(since 3.1.1, Bug #393094)

Grammar - two versions:

  • Full, 24h mode
#hh:nn:ss# 
#hh:nn:ss.mmm# 
  • Full, 12h mode
#hh:nn:ss{whitespace}AM# 
#hh:nn:ss{whitespace}PM# 
#hh:nn:ss.mmm{whitespace}AM# 
#hh:nn:ss.mmm{whitespace}PM# 
  • Short, 24h mode
#hh:nn#
#hh:nn.mmm#
  • Short, 12h mode
#hh:nn{whitespace}AM#
#hh:nn{whitespace}PM#
#hh:nn.mmm{whitespace}AM#
#hh:nn.mmm{whitespace}PM#

Notes:

  • ".mmm" stands for milliseconds: 1-3 digits, skip for zero milliseconds
  • At least one digit is required; e.g. both #05:59:01# and #5:59:1# are time literals that have the same value
  • {whitespace} before the AM or PM suffix is optional
  • AM/PM suffixes are case insensitive

Date/Time constants

(since 3.1.1, Bug #393094)

Grammar:

#{date}{whitespace}{time}#

Where {date} is defined as for Date literals and {time} is defined as for Time literals.

Notes:

  • All notes regarding date and time format also apply to data and time parts of the value