Home
SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
Guide to SQL: Syntax
The Informix® Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the Informix dialect of the SQL language.
Built-In Routines
BSON processing functions
BSON processing functions manipulate BSON data. Some SQL statements require function expressions that call these built-in functions.
BSON_VALUE_DATE function
The BSON_VALUE_DATE function is a built-in SQL function that converts values that correspond to a date in a BSON field to the DATE data type. The function returns integer string values.

SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
- Guide to SQL: Syntax
  The Informix® Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the Informix dialect of the SQL language.
  - Overview of SQL syntax
    These topics provide an overview about how to use the SQL statements, SPL statements, and syntax segments.
  - SQL statements
    This chapter describes the syntax and semantics of SQL statements that are recognized by Informix®.
  - SPL statements
    These topics describe Stored Procedure Language (SPL) statements, which you use to write SPL routines. You can store these routines in the database as user-defined routines (UDRs).
  - Data types and expressions
    These topics describe the data types and expressions that Informix®supports.
  - Other syntax segments
    These topics describe syntax segments, which are language elements, such as database object names or optimizer directives, that appear as a subdiagram reference in the syntax diagrams of some SQL or SPL statements.
  - Built-In Routines
    - Interval functions
      Use the interval functions to return an INTERVAL value from arguments that include a number and a character-string representing time units and separators in INTERVAL qualifiers. These functions are useful in CREATE TABLE, CREATE INDEX, and ALTER FRAGMENT statements that define or modify range-interval distributed storage strategies for indexes and tables.
    - Session Configuration Procedures
    - BSON processing functions
      BSON processing functions manipulate BSON data. Some SQL statements require function expressions that call these built-in functions.
      - BSON_GET function
        The BSON_GET function is a built-in SQL function that retrieves field-value pairs for the specified field in a BSON column.
      - BSON_SIZE function
        The BSON_SIZE function is a built-in SQL function that returns the storage size of a field in a BSON column. The size is returned as an integer, in units of bytes. If the field is not specified, the function returns the size of the entire BSON column.
      - BSON_UPDATE function
        The BSON_UPDATE function is a built-in SQL function that updates the contents of a BSON column with MongoDB update operators.
      - BSON_VALUE_BIGINT function
        The BSON_VALUE_BIGINT function is a built-in SQL function that converts a whole number, up to a maximum absolute size of 2⁶³ -1 bytes, in a BSON field to a BIGINT data type. The function returns an integer, in units of bytes.
      - BSON_VALUE_BOOLEAN function
        The BSON_VALUE_BOOLEAN function is a built-in SQL function that converts two-state values that correspond to a t (for true) or f (for false) in a BSON field to an opaque BOOLEAN data type. The function returns string values.
      - BSON_VALUE_DATE function
        The BSON_VALUE_DATE function is a built-in SQL function that converts values that correspond to a date in a BSON field to the DATE data type. The function returns integer string values.
      - BSON_VALUE_DOUBLE function
        The BSON_VALUE_DOUBLE function is a built-in SQL function that numeric values in a BSON field to the DOUBLE PRECISION data type. The function returns string values that require eight bytes of storage.
      - BSON_VALUE_INT function
        The BSON_VALUE_INT function is a built-in SQL function that converts a whole number, up to a maximum absolute size of 2³² -1 bytes, in a BSON field to an INT data type. The function returns integers in the range -2 147 483 647 to 2 147 483 647 bytes.
      - BSON_VALUE_LVARCHAR function
        The BSON_VALUE_LVARCHAR function is a built-in SQL function that converts a character string that is fewer than 32 740 bytes in a BSON field to an LVARCHAR data type. The function returns string values.
      - BSON_VALUE_OBJECTID function
        The BSON_VALUE_OBJECTID function is a built-in SQL function that returns the ObjectId values in a BSON document as strings.
      - BSON_VALUE_TIMESTAMP function
        The BSON_VALUE_TIMESTAMP function is a built-in SQL function that converts a time stamp in a BSON field to the built-in DATETIME data type.
      - BSON_VALUE_VARCHAR function
        The BSON_VALUE_VARCHAR function is a built-in SQL function that converts character strings whose size is fewer than 255 bytes in a BSON field to a VARCHAR data type. The function returns string values.
      - genBSON function
        The genBSON function provides an efficient method for copying data from an Informix® database into a JSON or BSON document store format.
    - DataBlade® Module Management Functions
      From sessions connected to Informix® databases that support explicit transaction logging, you can register or unregister DataBlade® modules by issuing SQL statements that call the built-in SYSBldPrepare( ) function. Another built-in function, SYSBldRelease( ), returns the version string of the SYSBldPrepare( ) function in the local database.
    - The EXPLAIN_SQL Routine
      The Data Studio Administration Edition can use the EXPLAIN_SQL routine to obtain a query plan in XML format, interpret the XML, and render the plan visually.
    - UDR Definition Routines
    - jvpcontrol Function
    - SQLJ Driver Built-In Procedures
    - Extended Utilities
  - Appendixes
- Guide to SQL: Reference
  The Informix® Guide to SQL: Reference contains the reference information for the system catalog tables, data types, and environment variables of the HCL Informix dialect of the SQL language, as implemented in HCL Informix. These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with HCL Informix.
- Guide to SQL: Tutorial
  The Informix® Guide to SQL: Tutorial shows how to use basic and advanced structured query language (SQL) to access and manipulate the data in your databases. It discusses the data manipulation language (DML) statements as well as triggers and stored procedure language (SPL) routines, which DML statements often use.

BSON_VALUE_DATE function

The BSON_VALUE_DATE function is a built-in SQL function that converts values that correspond to a date in a BSON field to the DATE data type. The function returns integer string values.

Use the BSON_VALUE_DATE function to return or operate on date and time data in a field in a BSON column with SQL statements.

Syntax

BSON_VALUE_DATE function

BSON_VALUE_DATE(bson_column ,"field")


Element	Description	Restrictions	Syntax
`bson_column`	Name of a BSON column	Must exist	Identifier
`field`	A string that represents a field name to search for in the BSON column. Can be a multilevel identifier.		Quoted String, Column Expressions

Usage

The field parameter can be a simple SQL identifier. The statements in the following example create a table with a BSON column and, insert three documents. The documents are explicitly cast to JSON and then implicitly cast to BSON.

CREATE DATABASE testdb WITH LOG;
CREATE TABLE IF NOT EXISTS bson_table(bson_col BSON);

INSERT INTO bson_table 
VALUES('{id:1234,join_date:ISODate("2013-05-18T00:33:14.000Z")}'::JSON);
INSERT INTO bson_table 
VALUES('{id:1235,join_date:ISODate("2012-09-12T00:13:44.000Z")}'::JSON);
INSERT INTO bson_table 
VALUES('{id:1236,join_date:ISODate("2014-08-10T00:11:44.000Z")}'::JSON);

The following statement returns the value of the id field for all dates earlier than October 5, 2012:

SELECT bson_col.id::json FROM bson_table 
WHERE bson_col.join_data::date >DATE("10/05/2012");

(expression){"id":1234}
(expression){"id":1236}

2 row(s) retrieved.

The following statements return the value of the join_date field:

SELECT bson_col.id::json, bson_value_date(bson_col, "join_date") FROM bson_table
WHERE bson_value_date(bson_col, "join_date")::date ='05/18/2013';

(expression)  {"id":1234}
(expression)  2013-05-18 00:33:14.00000

1 row(s) retrieved.

The following statement creates an index on the join_date field in the BSON column:

CREATE INDEX idx_joindt ON bson_table(bson_value_date(bson_col,"join_date"))
USING bson;

This example shows simple field-name value pairs, but the field parameter in BSON_VALUE_DATE expressions for BSON columns with more complex structures can be specified as a multilevel identifier in dot notation.