Difference between revisions of "Kexi/Plugins/Queries/SQL Constants"

< Kexi‎ | Plugins‎ | Queries
Jump to: navigation, search
(Date constants)
Line 7: Line 7:
 
== Date constants ==
 
== Date constants ==
  
(since 3.1.1, {{Bug|393094}})
+
(since 3.1.1, {{Bug|393094}}, {{Task|10134}})
  
 
Grammar - two versions:
 
Grammar - two versions:

Revision as of 10:09, 27 November 2018

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, Task #10134)

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

See also


Content is available under Creative Commons License SA 4.0 unless otherwise noted.