From c4b49fbddd2099733cb5c7606fbc5b848b23c1e0 Mon Sep 17 00:00:00 2001 From: Kay Schenk Date: Fri, 26 Sep 2014 09:31:26 +0100 Subject: Added information on date types to correspond to SQL2. Reformatted information a bit Change-Id: I0e9be1d68b78aa60f1eb3a30be708ff966ebaea1 --- source/text/shared/explorer/database/02010100.xhp | 98 ++++++++++++++--------- 1 file changed, 62 insertions(+), 36 deletions(-) diff --git a/source/text/shared/explorer/database/02010100.xhp b/source/text/shared/explorer/database/02010100.xhp index a317ab78e2..83a2849e04 100644 --- a/source/text/shared/explorer/database/02010100.xhp +++ b/source/text/shared/explorer/database/02010100.xhp @@ -319,6 +319,7 @@ Formulating filter conditions
When formulating filter conditions, various operators and commands are available to you. Apart from the relational operators, there are SQL-specific commands that query the content of database fields. If you use these commands in the $[officename] syntax, $[officename] automatically converts these into the corresponding SQL syntax. You can also enter the SQL command directly. The following tables give an overview of the operators and commands: + @@ -399,6 +400,7 @@
+ @@ -573,6 +575,15 @@ returns field names with the field content "Ms." + + + +<'2001-01-10' + + +returns dates that occurred before January 10, 2001" + + LIKE 'g?ve' @@ -615,73 +626,88 @@
+ + +Like Escape Sequence: {escape 'escape-character'} +Example: select * from Item where ItemName like 'The *%' {escape '*'} +The example will give you all of the entries where the item name begins with 'The *'. This means that you can also search for characters that would otherwise be interpreted as placeholders, such as *, ?, _, % or the period. + +Outer Join Escape Sequence: {oj outer-join} +Example: select Article.* from {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR} +Querying text fields +To query the content of a text field, you must put the expression between single quotes. The distinction between uppercase and lowercase letters depends on the database in use. LIKE, by definition, is case-sensitive (though some databases don't see it that strict). + +Querying date fields + -Date fields are represented as #Date# to clearly identify them as dates. The date condition will be reproduced in the resulting SQL statement in the following ODBC - compliant way: +Date fields are represented as #Date# to clearly identify them as dates. Date, time and date/time constants (literals) used in conditions can be of either the SQL Escape Syntax type, or default SQL2 syntax. + -Date - +Date Type Element + -{D'YYYY-MM-DD'} +SQL Escape syntax #1 - may be obsolete - - -Date time +SQL Escape syntax #2 -{D'YYYY-MM-DD HH:MM:SS'} +SQL2 syntax + -Time - +Date + -{D'HH:MM:SS'} +{D'YYYY-MM-DD'} - -
-$[officename] also supports the following Escape sequences known from ODBC and JDBC: - - -Date +{d 'YYYY-MM-DD'} -{d 'YYYY-MM-DD'} +'YYYY-MM-DD' -Time +Time -{t 'HH:MI:SS[.SS]'} - [ ] optional +{D'HH:MM:SS'} - + +{t 'HH:MI:SS[.SS]'} + + +'HH:MI:SS[.SS]' + + -DateTime +DateTime -{ts 'YYYY-MM-DD HH:MI:SS[.SS]'} - [ ] optional +{D'YYYY-MM-DD HH:MM:SS'} - + +{ts 'YYYY-MM-DD HH:MI:SS[.SS]'} + + +'YYYY-MM-DD HH:MI:SS[.SS]' + +
-Example: select {d '1999-12-31'} from world.years - -Like Escape Sequence: {escape 'escape-character'} -Example: select * from Item where ItemName like 'The *%' {escape '*'} -The example will give you all of the entries where the item name begins with 'The *'. This means that you can also search for characters that would otherwise be interpreted as placeholders, such as *, ?, _, % or the period. - -Outer Join Escape Sequence: {oj outer-join} -Example: select Article.* from {oj item LEFT OUTER JOIN orders ON item.no=orders.ANR} -Querying text fields -To query the content of a text field, you must put the expression between single quotes. The distinction between uppercase and lowercase letters depends on the database in use. LIKE, by definition, is case-sensitive (though some databases don't see it that strict). -Querying date fields -Even if you want to filter by a date, you must place the expression between single quotation marks. The following formats are valid: YYYY-MM-DD HH:MM:SS and YYYY/MM/DD HH:MM:SS as well as YYYY.MM.DD HH:MM:SS + + + Example: select {d '1999-12-31'} from world.years + Example: select * from mytable where years='1999-12-31' +All date expressions (literals) must be enclosed with single quotation marks. (Consult the reference for the particular database and connnector you are using for more details.) + + Querying Yes/No fields To query Yes/No fields, use the following syntax for dBASE tables: -- cgit v1.2.3