1 of 29

The Basics of CQL

This page will help you to understand the fundamentals of getting started with Cinchy Query Language.

1. Introduction

Cinchy Query Language (CQL) is a query language unique to dataware that is used to retrieve and manage/modify data and metadata from tables in your network. While data can reside across many tables, a query can isolate it to a single output, making the possibilities of CQL endlessly powerful.

Cinchy Query Language can be used in many ways, including but not limited to:

Building queries through the query editor that can return, insert, delete, and otherwise manage your data.
Creating, altering or dropping views for tables
Creating, altering or dropping indexes

For an overview of the supported functions of CQL,

All queries on Cinchy are automatically protected by universal data access controls. This means that if you run a CQL query, you will only see the data that you have been given access to.

2. Basic Rules of CQL

The following is a non-exhaustive list of some of the common things to keep in mind while using CQL. While CQL pulls many similarities to other queries languages such as SQL or PGSQL that you may already be familiar with, there are still differences to make note of.

Cinchy comes with an intuitive Query Builder UI. When using the builder, the below basic syntax is pre-written for you. You can then use the drag and drop interface or type in your own search terms to build your query.

In all queries built using the Cinchy Query Builder, you will note that it includes a "WHERE [Deleted] IS NULL" clause. This will prevent any data that has been deleted from a table from ending up in your query. If you want to include deleted data, you must delete this clause.
When querying a table, you must use the [Domain].[Table] syntax. For example, to query the Product Content Backlog table, you would use [Product].[Product Content Backlog].
When querying a linked column, you must similarly use the [Column Name].[Linked Column Name] syntax.
- For example, in the below query we are pulling from the [Product].[Product Content Backlog] table, and are looking for data from a linked column called "Requester", which points to the Users table. In order to return the [Full Name] column from the linked Users table, we must use the [Requester].[Full Name] syntax.

Multi-level links are also possible using a similar syntax of [Column Name].[Linked Column Name].[Linked Column Name]. For example, if you wanted to see the Manager ID of a specific employee, you could use [Employee].[Reports To].[Employee ID], which will find the Employee in question, then who they report to, then the ID number of that person.

When writing queries, single notation marks are used to denote string/text data. For example, if you wanted to specify the Sandbox domain, you would use [Domain] = 'Sandbox'
When trying to query using a "does not equal" syntax, you would use !=. For example, the following denotes to only return results where the [Domain] does not equal 'Sandbox'

If you want to query data from a table where certain rows are still in draft/Create Request format, you would use the syntax of Draft([Column Name]) to see the draft changes. Make sure to also include [Column Name] as well to include non-draft rows.
The default ORDER BY function will always set ascending unless specified.
When using a boolean query, 1 = true, and 0 = false.
Version history in Cinchy is labeled differently in other SQL systems. Normally a version history is shown through, for example, "version 1.2.4". Cinchy follows these conventions but splits them up. The two ORDER BY options, Version and Draft Data. are the version numbers. Version is the first number, and Draft Data is the second number in the sequence Example: [Version]: 2 and [Draft Data]: 5 means the overall version of the policy is 2.5
If there is an error in your CQL code an error message will appear in your Query Results in the query builder indicating what column and row your error(s) reside in.
When using an INSERT INTO clause, the order of the columns input must match the same order as in the VALUES section.
Putting a * symbol after a SELECT statement in the Query Builder will return a series of system columns attached to each table entry.

Use RESOLVELINK when inserting or updating values for link columns. In the below example, we use (RESOLVELINK(@Manager,'Full Name')) because the Manager column is a linked column pulling from the Employee table. The syntax for using Resolve Link is: ResolveLink('value(s)','column in target table')

3. Query Return Results

You can specify what your results return as in the Query Builder

3. Data Return Types

The following tables provide the data type(s) that a Cinchy Data Type translates to in the database:

CQL Examples

This page provides some examples of Cinchy Query Language in action.

1. CQL Examples

The following section breaks down some example queries for you to get a feel for the power of CQL and how to use it. These are just simple examples, but will provide you with a jumping off point for creating your own, more complex queries.

For more information on saved queries, see our Builder Guide, here.

1.1 Creating a list of specific users

This query is useful for pulling a list of specific user information from a table. This query will return a list with Names (which will be returned under the 'Full Name' label), Emails, and Companies of the users listed in the [Contacts].[People] table. We have stipulated to only include entries where the [Name] is not null, and where the [Tags].[Name] column is set to 'Analyst'

Syntax

SELECT [Column1] as 'Alias',[Column2], [Column3]
FROM [Domain].[Table]
WHERE [Deleted] IS NULL
AND [Column1] IS NOT NULL
AND [Column4].[Value1]= 'Value2'
ORDER BY [Column1]

Example

SELECT [Name] as 'Full Name',[Email], [Company]
FROM [Contacts].[People]
WHERE [Deleted] IS NULL
AND [Name] IS NOT NULL
AND [Tags].[Name]= 'Analyst'
ORDER BY [Name]

1.2 Finding all tables in a specific domain

In this example we want to find all System Tables in our instance. System Tables come pre-packaged with Cinchy and live under the 'Cinchy' domain. You can query for a list of them using the below CQL, where we are pulling the 'Name' column from the [Tables] table, in the [Cinchy] Domain. We have added a filter to only return results where the table [Domain] is tagged as 'Cinchy'.

Syntax

SELECT [Column1]
FROM [Domain].[Table]
WHERE [Deleted] IS NULL
AND [Domain] = 'Value1'

Example

SELECT [Name]
FROM [Cinchy].[Tables]
WHERE [Deleted] IS NULL
AND [Domain] = 'Cinchy'

1.3 Finding system columns attached to a table

In this example, we want to generate a list of the system columns attached to the [Policies] table in the [Compliance] domain. We do so by putting the * symbol in the SELECT statement. We have also added an option ORDER BY clause, to order our results based on the input values.

Syntax

SELECT * 
FROM [Domain].[Table] 
WHERE [Deleted] IS NULL
ORDER BY [Column1], [Column2], [Column3], [Column4]

Example

SELECT * 
FROM [Compliance].[Policies] 
WHERE [Deleted] IS NULL
ORDER BY [Cinchy Id], [Title], [Version], [Draft Version]

1.4 Creating a View that is tailored to the user who is logged in

In this example, we have a table with a view for "My Open Tasks". We want this view to only show tasks that are assigned to the currently logged in user, so we would use currentuserID=().

[Assignee].[Person].[Cinchy User Account].[Cinchy Id] = currentuserID()

1.5 Finding the owner of a table

The below query is used to find the creator of any specific table, based on its tablecinchyid. We are pulling the Table Name and the Created By user information from the [Cinchy].[Tables] table. By using [Cinchy Id] = @tablecinchyid, a search box will appear when we run this query, where you can insert the Table ID number of the table you are curious about.

SELECT [Name], [Created By]
FROM [Cinchy].[Tables]
WHERE [Deleted] is null and [Cinchy Id] = @tablecinchyid

1.6 Finding all tables created by a specific user

In the below example, we are using a query to return a list of all tables created by a specific user. We are pulling the Full Name of the table and the name of the creator from the [Cinchy].[Tables] table. By using [Created By].[Name] = @author, running the query will generate a search box where you can write in the full name of the user you are searching for information on.

SELECT [Full Name], [Created By].[Name]
FROM [Cinchy].[Tables]
WHERE [Deleted] IS NULL AND [Created By].[Name] = @author

1.7 Using If/Else statements

If/Else statements can be used in CQL to specify conditions. In the below example, we are using a query to vote for user awards, and our IF statements are looking for the currentuserid() to determine the results of the query.

IF( @NOMINEE_USER  = currentuserid())
Begin
SELECT 'Hey! No voting for yourself!'
 End
ELSE
IF( @CREATEDBY_USER = currentuserid())
Begin
SELECT 'Hey! No voting for your own nomination!'
 End
 ELSE
Begin
   		INSERT INTO [Employee Success].[BASICs In Action Votes] ([Nomination], [Vote]) VALUES (ResolveLink(@NOMINATION_ID, 'Cinchy Id'), @VOTE)
		SELECT 'Your vote has been recorded. Great work!'
End

If the current user is the same as the nominated user, the query will return a message stating "Hey! No voting for yourself!"

IF( @NOMINEE_USER  = currentuserid())
Begin
SELECT 'Hey! No voting for yourself!'
 End

Our second condition checks whether the current user is the same as the user who created the nomination. In this case, the query will return a message stating "Hey! No voting for your own nomination!"

ELSE
IF( @CREATEDBY_USER = currentuserid())
Begin
SELECT 'Hey! No voting for your own nomination!'
 End

If neither of the two IF statements apply, the final ELSE is triggered to capture the vote (using the INSERT INTO statement), and a message is returned stating "Your vote has been recorded. Great work!"

 ELSE
Begin
   		INSERT INTO [Employee Success].[BASICs In Action Votes] ([Nomination], [Vote]) VALUES (ResolveLink(@NOMINATION_ID, 'Cinchy Id'), @VOTE)
		SELECT 'Your vote has been recorded. Great work!'
End

1.8 Finding all Deleted Tables

This query returns the Name, Domain, and timestamp of deletion for all tables within [Cinchy].[Tables] where [Deleted] IS NOT NULL. We have added an option WHERE clause to ignore any data from the 'Sandbox' Domain.

SELECT [Name], [Domain], [Deleted]
FROM [Cinchy].[Tables]
WHERE [Deleted] IS NOT NULL 
	AND [Domain] != 'Sandbox'

CQL Functions Master List

1. Overview

The following living list documents Cinchy Query Language functions. Click on the function name to be taken to its individual documentation page.Please note that some CQL functions are currently "in progress" for support on PGSQL deployments. Please check back regularly for the updated list.

2. CQL Functions

CQL Statements Overview

1. Overview

The Cinchy Query Language (CQL) statements are grouped into two (2) categories:

Data Manipulation Language (DML) - DML is used to add, retrieve, update and manipulate data. For example, insert, update, delete.
Data Definition Language (DDL) - DDL is used to create a database schema and define constraints. For example, create a table and alter a table.

2. Data Manipulation Language (DML)

List of available DML statements:

3. Data Definition Languages (DDL)

List of available DDL statements:

Cinchy DML Statements

1. Overview

DML is used to add, retrieve, update and manipulate data.The Cinchy DML statements covered on this page are:

SELECT
INSERT
UPDATE
DELETE
IF
Declare Variables
Set Variables

SELECT

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

Note: Table name requires domain prefix.

Syntax

SELECT [Column1],[Column2], ...
FROM [Domain].[Table_Name]

Example

SELECT [Full Name], [Email Address], [Start Date]
FROM [HR].[Employee]

Nested JSONs

You can create a query that will produce a nested JSON by wrapping it in an outer SELECT statement, such as in the example below.

Note that you should set the return type to "Single Value (First Column of First Row)".

Example Statement

SELECT (
SELECT 
(SELECT p1.[Parent A],p1.[Parent B] 
FROM [QA].[Parents 5080] p1 WHERE p1.[Parents]=p.[Parents]
FOR JSON PATH) As Parents,
(SELECT c.[Name] AS [Child Name]
FROM [QA].[Children 5080] c
WHERE c.[Deleted] IS NULL AND c.[Parents]= p.[Parents]
FOR JSON PATH) AS Children
FROM [QA].[Parents 5080] p
WHERE p.[Deleted] IS NULL
FOR JSON PATH, INCLUDE_NULL_VALUES)

Example Output

[
  {
    "Parents": [{ "Parent A": "Tom", "Parent B": "Maria" }],
    "Children": [
      { "Child Name": "John" },
      { "Child Name": "Theodor" },
      { "Child Name": "Lynette" }
    ]
  },
  {
    "Parents": [{ "Parent A": "Ray", "Parent B": "Sofia" }],
    "Children": [{ "Child Name": "Stephen" }]
  },
  {
    "Parents": [{ "Parent A": "Greg", "Parent B": "Mona" }],
    "Children": [{ "Child Name": "Elizabeth" }, { "Child Name": "Martin" }]
  },
  {
    "Parents": [{ "Parent A": "Henry", "Parent B": "Susanne" }],
    "Children": null
  }
]

INSERT

An INSERT statement can be used to add new rows to a table or view
You can also include a SELECT statement to identify that another table or view contains the data for the new row or rows.

Note: The table name requires the domain prefix.

Syntax

INSERT INTO [Domain].[Table_Name] ([Column1],[Column2],[Column3], ...)
VALUES ([Value1],[Value2],[Value3], ...)

Example

INSERT INTO [Contacts].[People] ([First Name],[Last Name],[Address],[Job Title])
VALUES (@firstname, @lastname, @address, @jobtitle)

UPDATE

The data in a table can be changed by using the UPDATE statement.

The UPDATE statement modifies zero or more rows of a table, depending on how many rows satisfy the search condition that was specified in the WHERE clause.

An UPDATE statement can also be used to specify the values that are to be updated in a single row.

This is done by specifying constants, host variables, expressions, DEFAULT, or NULL. Specify NULL to remove a value from a row's column (without removing the row).

Note: Table name requires domain prefix.

Syntax

UPDATE [Domain].[Table_Name]
SET [Column1] = [Value1], [Column2] = [value2], ...
WHERE [condition]

Example

UPDATE [Revenue].[Customers]
SET [ContactName] = 'Alfred Schmidt', [City]= 'Frankfurt'
WHERE [Customer_ID] = 1;

DELETE

A DELETE statement can be used to remove entire rows from a table. The number of rows deleted depends on how many rows satisfy the search condition specified in the WHERE statement.

Note: Table name requires domain prefix.

Syntax

DELETE FROM [Domain].[Table_Name] WHERE [condition]

Example

DELETE FROM [Revenue].[Customers] WHERE [CustomerName] = 'Alfreds Futterkiste';

IF

The IF statement is used to execute a condition. If the condition is satisfied then the boolean expressions returns TRUE value. The optional ELSE keyword introduces another statement that is executed when the IF condition is not satisfied and returns FALSE value.

Syntax

IF [condition] THEN [value_if_true] ELSE [value_if_false]

Example

IF 500<1000 THEN 'YES' ELSE 'NO'

Declare Variable

The DECLARE statement is used to declare a variable.

Syntax

DECLARE @variable_name variable_type;

Example

DECLARE @var varchar(50);

Set Variable

The SET variable is used to set a value to a variable.

Syntax

SET @variable_name = variable_value

Example

SET @var = 'Alfreds Futterkiste'

Cinchy DDL Statements

1. Overview

DDL is used to create a database schema and define constraints.The Cinchy DDL functions covered on this page are:

Data Type Translations

The following tables provide the data type(s) that a Cinchy Data Type translates to in the database:

Create Table

The CREATE TABLE statement is used to create a new table in a database.

The column parameters specify the names of the columns of the table.The datatype parameter specifies the type of data the column can hold (e.g. varchar, char, int, date, etc.).

Syntax

Example

Alter Table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Syntax

Example

Drop Table

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

Example

Create View

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, taken from fields of an original table and presents them with a different structure and organization. For example, this is useful when only some columns of a table may be relevant to a user, so they don't have to see information from other columns that may be irrelevant to them.

SQL functions and the WHERE statement can be added to a view to present the data as if the data was coming from one single table.

Syntax

Example

Alter View

Modifies a previously created view. This includes an indexed view. ALTER VIEW does not affect dependent stored procedures or triggers and does not change permissions.

Syntax

Example

Drop View

A view is deleted with the DROP VIEW command.

Syntax

Example

Create Index

Syntax

Example

Drop Index

The DROP INDEX statement is used to delete an index on a table.

Syntax

Example

Cinchy Functions

1. Overview

The Cinchy functions covered in this section are:

resolveLink()

The resolveLink function is used to insert or update values for link columns. It can be used either with values in the target table or by using the Cinchy Id.

Using resolveLink against non-unique data values may not return the same data response each time; in that scenario we recommend that you resolve using the CinchyID instead.

Syntax

Example

currentUserID()

The currentUserID() function returns the currently logged in user's Cinchy Id. This is useful for setting up views as well as permissions.

Syntax

Example

You can use the below example in a view filter so that only the currently logged in user's tasks will appear.

currentUsersGroups()

The currentUsersGroups() function returns a list of the Cinchy Ids of groups that the current user belongs to, including any parent groups. For example, if a user is in the Cinchy Product group and the Cinchy Product group is under Cinchy Employees, then both will be returned.

Syntax

Example

executeSavedQuery()

The executeSavedQuery function returns a scalar or list of scalar values from the saved query specified as the parameters of the function. There are two optional parameters for this function: CacheTimeout and RecordLimitForReturn.

Syntax

Example (Single Value)

Example (Multi Value)

Optional Parameter: Timeout

A cache expiry timeout for executeSavedQuery is an additional option that can be added. Simply add the number of seconds as a 3rd parameter to the function.

Optional Parameter: Record Limit

The RecordLimitForReturn parameter limits the amount of records returned. For example: If the query returns 10 records, but you set the parameter to 5, then you will get the first five records back.

GetLastModifiedBy()

The GetLastModifiedBy([Column]) function will return the CinchyID of the user who last modified the specified column. It is currently only supported in SELECT statements.

Syntax

Example

This example will return the CinchyID of the user who last modified the Name column in the Employees table.

draft([Column Name])

This function allows you to query for draft values on tables where Change Approval is enabled.

When querying for draft data, the query result type needs to be set to "Query Results (including Draft Data)"

Syntax

Example

In this example, we want to query all data in the Employees table, including the data that is pending a change request (Image 1).

To return results that include the draft changes in the First Name column, we would set our query results to Include Draft Data, and use the following syntax (Image 2):

Cinchy System Values

1. Overview

The Cinchy system values covered in this section:

@cinchy_row_id

The @cinchy_row_id function returns the cinchy ID of the last-inserted row.

Syntax

Examples

Cinchy User Defined Functions

1. Overview

User Defined Functions provide customers with a way to specify and use more particular logic in your solutions than plain CQL may allow. It can be used to simplify calculations and orchestrate automations to accommodate your business requirements.

UDFs are written in Javascript.

There are two (2) groups of Cinchy User Defined Functions (UDF's):

Table-Valued Functions - Similar to the SQL construct of table-valued functions, a Cinchy User Defined Function can be SELECTED or CROSS JOINED from as if it is a table.
Scalar-Valued Functions - Similar to the SQL construct of scalar-valued functions. A Scalar-valued function in Cinchy is used to return a single value of any CQL data type. The function body can execute any JavaScript logic.

Cinchy UDFs run https://github.com/sebastienros/jint, which uses ECMAScript 5.1.

If you are having issues with your script, we suggest pasting it into https://jshint.com/, a tool that helps to detect errors and potential problems in your JavaScript code, as it also runs ECMAScript 5.1.

User Defined Functions (UDFs) are registered in the Cinchy User Defined Functions table (Image 1).

2. Structure

A user defined function in Cinchy is written in Javascript, and comes in the form of:

function name(parameter1, parameter2, parameter3,...) {
  // code to be executed
  return something;
}

It can perform external API calls and execute Cinchy Queries. Generally, at least something should be returned as an indication of success or failure even if you do not want to return any values.

Helper functions can be created within a UDF, however you cannot reference other UDFs in your UDF.

3. Imports

To use advanced functions in UDFs, import the following.

var helpers = importNamespace('Cinchy.UDFExtensions');
var adoNet = importNamespace('Cinchy.AdoNet');
var sysData = importNamespace('System.Data');

4. Cinchy Functions in UDFs

The following functions can be used in a Cinchy User Defined Function (but not in CQL directly).

4.1 External API Call

An XMLHttpRequest() helper can be used to help POST or GET data from an external API. Note that Cinchy basicAuthAPIs can also be accessed this way.

Supported Methods

var xmlHttp = new helpers.XMLHttpRequest();
    xmlHttp.open('POST', 'yourURLhere', false);
    xmlHttp.setRequestHeader('Content-Type', 'application/json');
    xmlHttp.setRequestHeader('apikey', 'yourapikeyhere');
    xmlHttp.send(JSON.stringify(payload));
    if (xmlHttp.status === 200) {
        var response = JSON.parse(xmlHttp.responseText);
        return response.result;
    } else {
        return 'Request failed.';
    }

4.2 Cinchy Query Call

A Cinchy query or a non query (not expecting a result back) can be executed in a UDF as well.

Supported Methods

function mainFunction(query,p1,p2) {
    var sysData = importNamespace('System.Data');
    var param = [];
    param.push(generateCinchyParam('parameterName1', sysData.DbType.String, p1.toString()));
    param.push(generateCinchyParam('parameterName2', sysData.DbType.Double, p2));

    var dataTable = [];
    dataTable = Query.executeQuery(query, param, null, null);
    
    return getSingleValue(dataTable,'colName');
}

function generateCinchyParam(name, type, value){
    var adoNet = importNamespace('Cinchy.AdoNet');
    cinchyParam = new adoNet.CinchyParameter('@' + name, type);
    cinchyParam.value = value;
    return cinchyParam;
}

function getSingleValue(dataTable,colName) {
    var sysData = importNamespace('System.Data');
    var result = '';
    var enumerator = dataTable.Rows.GetEnumerator();
    while (enumerator.MoveNext()) {
        var record = enumerator.Current;
        result = record[colName];
    }
    return result;
}

Table-Valued Functions

1. Overview

Similar to the SQL construct of table-valued functions, a Cinchy User Defined Function can be SELECTED or CROSS JOINED from -- as if it is a table.

2. Using a Table Valued UDF in CQL

The SELECT and FROM clause work the same for a table-valued UDF as they would for a regular Cinchy table.

3. Creating a Table in a UDF

To generate a table within a UDF for use in CQL, a dataTable will need to be created in the same format as the default Cinchy JSON Saved Query response (Image 1).

Scalar-Valued Functions

1. Overview

Similar to the SQL construct of scalar-valued functions. A Scalar-valued function in Cinchy is used to return a single value of any CQL data type. The function body can execute any JavaScript logic.

2. Using a Scalar-Valued UDF in CQL

Select a scalar value UDF as a column (Image 1).

Sample UDF

function demo(param1, param2) { 
    return param1 + ' ' + param2;
}

Sample Query

SELECT [Domain], [Name], demo([Domain],[Name]) as 'Full Name'
FROM [Cinchy].[Tables]
WHERE [Domain] = 'Cinchy'

Scalar-valued functions have to be invoked with a parameter, even if the definition of the function does not require a parameter. You can pass a string:

SELECT my_scalar('a') FROM [Cinchy].[Tables] WHERE [Deleted] IS NULL AND [Cinchy Id]=1

3. Using a Scalar-Valued UDF in a Calculated Column

Once it is confirmed that the UDF returns the expected result though a query, a calculated column can be include.

3.1 For Calculations

Depending on how intensive or live you want the calculation to be, choose whether to make it a live or cached calculated column.

Simply add your UDF to the calculated column (Image 2).

3.2 As a Trigger

To use the UDF to trigger an action (ex. create a row in another table), it should be a cached calculated column. Here are a few scenarios to watch out for:

Do not trigger when you do not have all the necessary fields
Do not trigger when non-relevant data on the row changes
- Make sure to appropriately insert and/or update in another table

Conversion Functions

1. Overview

These functions support data type casting and conversion. Conversion functions convert an expression of one data type to another data type. The conversion functions covered in this section are:

CAST

This function is used with CONVERT to convert an expression of one data type to another.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations.

Syntax

Arguments

expression Any valid expression

data_type The target data type.

length An optional integer that specifies the length of the target data type, for data types that allow a user specified length.

Return Types

Returns expression, translated to data_type

Example

CONVERT

This function is used with CAST to convert an expression of one data type to another.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Example

Arguments

expression Any valid expression

data_type The target data type.

length An optional integer that specifies the length of the target data type, for data types that allow a user specified length.

style An optional integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

Converting datetime to character:

Converting float to real:

Converting money to character:

Example

Date and Time Types and Functions

1. Overview

Perform operations on a date and time input values and return string, numeric, or date and time values.

The date and time date type and functions covered in this section are:

Date and Time Data Types
Date and Time Function Categories

Date and Time Data Types

Return System Date and Time Values

1. Overview

SQL derives all system date and time values from the operating system of the computer on which the instance of SQL Server runs.

SQL Server 2019 (15.x) derives the date and time values through use of the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server running. This API has a precision fixed at 100 nanoseconds. Use the GetSystemTimeAdjustment() Windows API to determine the accuracy.The return system date and time value functions covered in this section are:

SYSDATETIME
SYSDATETIMEOFFSET
SYSUTCDATETIME

SYSDATETIME

SYSDATETIME returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

SYSDATETIME ( )

Return Types

datetime2(7)

Remarks

SQL statements can refer to SYSDATETIME anywhere they can refer to a datetime2(7) expression.

SYSDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

Example 1: Getting the Current System Date and Time

SELECT
  SYSDATETIME(),
  SYSDATETIMEOFFSET(),
  SYSUTCDATETIME(),
  GETDATE(),
  GETUTCDATE()

/*
Returned:
SYSDATETIME()        2020-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()  2020-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME()     2020-04-30 20:10:02.0474381
GETDATE()            2020-04-30 13:10:02.047
GETUTCDATE()         2020-04-30 20:10:02.047  
*/

Example 2: Getting the Current System Date

SELECT
    CONVERT(date, SYSDATETIME()),
    CONVERT(date, SYSDATETIMEOFFSET()),
    CONVERT(date, SYSUTCDATETIME()),
    CONVERT(date, GETDATE()),
    CONVERT(date, GETUTCDATE())

/* All returned 2020-04-30 */

Example 3: Getting the Current System Time

SELECT
    CONVERT(time, SYSDATETIME()),
    CONVERT(time, SYSDATETIMEOFFSET()),
    CONVERT(time, SYSUTCDATETIME()),
    CONVERT(time, GETDATE()),
    CONVERT(time, GETUTCDATE())

/*
Returned:
SYSDATETIME()        13:18:45.3490361
SYSDATETIMEOFFSET()  13:18:45.3490361
SYSUTCDATETIME()     20:18:45.3490361
GETDATE()            13:18:45.3470000
GETUTCDATE()         20:18:45.3470000  
*/

SYSDATETIMEOFFSET

SYSDATETIMEOFFSET returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

SYSDATETIMEOFFSET()

Return Types

datetimeoffset(7)

Remarks

SQL statements can refer to SYSDATETIMEOFFSET anywhere they can refer to a datetimeoffset expression.

SYSDATETIMEOFFSET is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

Example 1: Showing Formats Returned by the Date and Time Functions

SELECT
    SYSDATETIME() AS [SYSDATETIME()],
    SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()],
    SYSUTCDATETIME() AS [SYSUTCDATETIME()],
    GETDATE() AS [GETDATE()],
    GETUTCDATE() AS [GETUTCDATE()]

Example 2: Converting Date and Time to Date

SELECT
    CONVERT(date, SYSDATETIME()),
    CONVERT(date, SYSDATETIMEOFFSET()),
    CONVERT(date, SYSUTCDATETIME()),
    CONVERT(date, GETDATE()),
    CONVERT(date, GETUTCDATE())

Example 3: Converting Date and Time to Times

SELECT
    CONVERT(time, SYSDATETIME()) AS [SYSDATETIME()],
    CONVERT(time, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()],
    CONVERT(time, SYSUTCDATETIME()) AS [SYSUTCDATETIME()],
    CONVERT(time, GETDATE()) AS [GETDATE()],
    CONVERT(time, GETUTCDATE()) AS [GETUTCDATE()]

SYSUTCDATETIME

SYSUTCDATETIME returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time are returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here .

Syntax

SYSUTCDATETIME()

Return Types

datetime2

Remarks

SQL statements can refer to SYSUTCDATETIME anywhere they can refer to a datetime2 expression.

SYSUTCDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

Example 1: Showing Formats Returned by Date and Time functions

SELECT
    SYSDATETIME() AS [SYSDATETIME()],
    SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()],
    SYSUTCDATETIME() AS [SYSUTCDATETIME()],
    GETDATE() AS [GETDATE()],
    GETUTCDATE() AS [GETUTCDATE()]

Example 2: Converting Date and Time to Date

SELECT
    CONVERT(date, SYSDATETIME()),
    CONVERT(date, SYSDATETIMEOFFSET()),
    CONVERT(date, SYSUTCDATETIME()),
    CONVERT(date, GETDATE()),
    CONVERT(date, GETUTCDATE())

Example 3: Converting Date and Time to Time

DECLARE @DateTime DATETIME = GETDATE()
DECLARE @Time TIME

SELECT
    @Time = CONVERT(time, @DateTime)

SELECT
    @Time AS 'Time',
    @DateTime AS 'Date Time'

Return Date and Time Parts

1. Overview

The return date and time part functions covered in this section are:

DATENAME

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations.

The DATENAME function returns a character string representing the specified datepart of the specified date.

Syntax

Arguments

datepart The specific part of the date argument that DATENAME will return. This table lists all valid datepart arguments.

date

An expression that can resolve to one of the following data types:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

For date, DATENAME will accept a column expression, expression, string literal, or user-defined variable. Use four-digit years to avoid ambiguity issues.

Return Types

Remarks

Use DATENAME in the following clauses:

GROUP BY
HAVING
ORDER BY
SELECT <list>
WHERE

Examples

SELECT DATENAME(datepart,'2007-10-30 12:15:32.1234567 +05:10');

Result Set

DATEPART

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

DATEPART function returns an integer representing the specified datepart of the specified date.

Syntax

Arguments

datepart The specific part of the date argument for which DATEPART will return an integer. This table lists all valid datepart arguments.

date An expression that resolves to one of the following data types:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

For date, DATEPART will accept a column expression, expression, string literal, or user-defined variable. Use four-digit years to avoid ambiguity issues.

Return Type

Remarks

DATEPART can be used in the select list, WHERE, HAVING, GROUP BY, and ORDER BY clauses.

DATEPART implicitly casts string literals as a datetime2 type in SQL Server 2019 (15.x). This means that DATENAME does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Example 1: Returns Base Year

Example 2: Returns Day the Day Part of the Date

Example 3: Returns the year Part of the Date

DAY

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

DAY function returns an integer that represents the day (day of the month) of the specified date.

Syntax

Arguments

date An expression that resolves to one of the following data types:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

For date, DAY will accept a column expression, expression, string literal, or user-defined variable.

Return Types

Example 1:

This returns 30 - the number of the day itself

Example 2:

This statement returns 1900, 1, 1. The date argument has a number value of 0. SQL Server interprets 0 as January 1, 1900.

MONTH

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

MONTH returns an integer that represents the month of the specified date.

Syntax

Arguments

date Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable, or string literal.

Return Types

Example 1:

The following statement returns 4. This is the number of the month.

Example 2:

The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.

YEAR

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

YEAR function returns an integer that represents the year of the specified date.

Syntax

Arguments

Return Types

Example 1:

The following statement returns 2020. This is the number of the year.

Example 2:

The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.

Return Date and Time Values From Their Parts

1. Overview

The following return date and time values from their parts functions covered in this section are:

DATEFROMPARTS

This function returns a date value that maps to the specified year, month, and day values.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month, from 1 to 12.

day An integer expression that specifies a day.

Return Types

Remarks

DATEFROMPARTS returns a date value, with the date portion set to the specified year, month and day, and the time portion set to the default. For invalid arguments, DATEFROMPARTS will raise an error. DATEFROMPARTS returns null if at least one required argument has a null value.

Example

DATETIME2FROMPARTS

DATETIME2FROMPARTS function returns a datetime2 value for the specified date and time arguments. The returned value has a precision specified by the precision argument.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies the hours.

minute An integer expression that specifies the minutes.

seconds An integer expression that specifies the seconds.

fractions An integer expression that specifies a fractional seconds value.

percision An integer expression that specifies the precision of the datetime2 value that DATETIME2FROMPARTS will return.

Return Types

Remarks

DATETIME2FROMPARTS returns a fully initialized datetime2 value. DATETIME2FROMPARTS will raise an error if at least one required argument has an invalid value. DATETIME2FROMPARTS returns null if at least one required argument has a null value. However, if the precision argument has a null value, DATETIME2FROMPARTS will raise an error.

The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIME2FROMPARTS will raise an error.

Example 1: Without Fractions of a Second

Example 2: With Fractions of a Second

When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.
When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.
When fractions have a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

DATETIMEFROMPARTS

DATETIMEFROMPARTS function returns a datetime value for the specified date and time arguments.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies hours.

minute An integer expression that specifies minutes.

seconds An integer expression that specifies seconds.

milliseconds An integer expression that specifies milliseconds.

Return Types

Remarks

DATETIMEFROMPARTS returns a fully initialized datetime value. DATETIMEFROMPARTS will raise an error if at least one required argument has an invalid value. DATETIMEFROMPARTS returns null if at least one required argument has a null value.

Example

DATETIMEOFFSETFROMPARTS

Returns a datetimeoffset value for the specified date and time arguments. The returned value has a precision specified by the precision argument and an offset as specified by the offset arguments.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies hours.

minute An integer expression that specifies minutes.

seconds An integer expression that specifies seconds.

frcations An integer expression that specifies a fractional seconds value.

hour_offset An integer expression that specifies the hour portion of the time zone offset.

minute_offset An integer expression that specifies the minute portion of the time zone offset.

precision An integer literal value that specifies the precision of the datetimeoffset value that DATETIMEOFFSETFROMPARTS will return.

Return Types

Remarks

DATETIMEOFFSETFROMPARTS returns a fully initialized datetimeoffset data type. The offset arguments represent the time zone offset. For omitted offset arguments, DATETIMEOFFSETFROMPARTS assumes a time zone offset of 00:00 - in other words, no time zone offset. For specified offset arguments, DATETIMEOFFSETFROMPARTS expects values for both arguments, and both values positive or negative. If minute_offset has a value and hour_offset has no value, DATETIMEOFFSETFROMPARTS will raise an error. DATETIMEOFFSETFROMPARTS will raise an error if the other arguments have invalid values. If at least one required arguments have a NULL value, then DATETIMEOFFSETFROMPARTS will return NULL. However, if the precision argument has a NULL value, then DATETIMEOFFSETFROMPARTS will raise an error.

The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIMEOFFSETFROMPARTS will raise an error.

Example 1: Without Fractions of a Second

Example 2: With Fractions of a Second

This example shows the use of the fractions and precision parameters:

When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.
When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.
When fractions have a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SMALLDATETIMEFROMPARTS

SMALLDATETIMEFROMPARTS returns a smalldatetime value for the specified date and time.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

year Integer expression specifying a year.

month Integer expression specifying a month.

day Integer expression specifying a day.

hour Integer expression specifying hours.

minute Integer expression specifying minutes.

Return Types

Remarks

This function acts as a constructor for a fully initialized smalldatetime value. If the arguments are not valid, then an error is thrown. If required arguments are null, then null is returned.

Example

TIMEFROMPARTS

TIMEFROMPARTS returns a time value for the specified time and with the specified precision.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

hour Integer expression specifying hours.

minute Integer expression specifying minutes.

seconds Integer expression specifying seconds.

fractions Integer expression specifying fractions.

precision Integer literal specifying the precision of the time value to be returned.

Return Types

Remarks

TIMEROMPARTS returns a fully initialized time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

Example 1: Without Fractions of a Second

Example 2: With Fractions of a Second

The following example demonstrates the use of the fractions and precision parameters:

When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.
When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.
When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

Return Date and Time Difference Values

1. Overview

The return date and time difference value functions covered in this section are:

DATEDIFF

DATDIFF function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations.

Syntax

Arguments

datepart The units in which DATEDIFF reports the difference between the startdate and enddate. Commonly used datepart units include month or second.

The datepart value cannot be specified in a variable, nor as a quoted string like 'month'.

The following table lists all the valid datepart values. DATEDIFF accepts either the full name of the datepart, or any listed abbreviation of the full name.

startdate An expression that can resolve to one of the following values:

date
datetime
datetimeoffset
smalldatetime
time

Use four-digit years to avoid ambiguity.

Return Types

Remarks

Use DATEDIFF in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

DATEDIFF may overflow with a precision of minute or higher if the difference between enddate and startdate returns a value that is out of range for int.

Example 1

Finding the number of days between two dates.

Example 2

Specifying user-defined variables for startdate and enddate

Example 3: Specifying scalar system functions for startdate and enddate

DATEDIFF_BIG

DATEIFF_BIG function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

datepart The part of startdate and enddate that specifies the type of boundary crossed.

This table lists all valid datepart argument names and abbreviations.

startdate An expression that can resolve to one of the following values:

date
datetime
datetimeoffset
smalldatetime
time

For date, DATEDIFF_BIG will accept a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a datetime. Use four-digit years to avoid ambiguity issues. DATEDIFF_BIG subtracts startdate from enddate. To avoid ambiguity, use four-digit years.

Return Types

Remarks

Use DATEDIFF_BIG in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF_BIG implicitly casts string literals as a datetime2 type. This means that DATEDIFF_BIG does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

DATEDIFF_BIG may overflow with a precision of nanosecond if the difference between enddate and startdate returns a value that is out of range for bigint.

Example

This example uses different types of expressions as arguments for the startdate and enddate parameters. It calculates the number of day boundaries crossed between dates in two columns of a table.

Modify Date and Time Values

1. Overview

The modify date and time value functions covered in this section are:

DATEADD

DATEADD function adds a specified number value to a specified datepart of an input date value, and then returns that modified value.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations.

Syntax

Arguments

datepart The part of date to which DATEADD adds an integer number. This table lists all valid datepart arguments.

number An expression that can resolve to an int that DATEADD adds to a datepart of date. DATEADD accepts user-defined variable values for number. DATEADD will truncate a specified number value that has a decimal fraction. It will not round the number value in this situation.

date An expression that can resolve to one of the following values:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

For date, DATEADD will accept a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a datetime. Use four-digit years to avoid ambiguity issues.

Return Types

The return value data type for this method is dynamic. The return type depends on the argument supplied for date. If the value for date is a string literal date, DATEADD returns a datetime value. If another valid input data type is supplied for date, DATEADD returns the same data type. DATEADD raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part.

Example 1

Incrementing datepart by an interval of 1

Example 2

Incrementing more than one level of datepart in one statement

Each of these statements increments datepart by a number large enough to additionally increment the next higher datepart of date:

Example 3

Using expressions as arguments for the number and date parameters

Specifying a column as date

This example adds 2 (two) days to each value in the OrderDate column, to derive a new column named PromisedShipDate:

EOMONTH

The EOMONTH function returns the last day of the month containing a specified date, with an optional offset.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

start_date A date expression that specifies the date for which to return the last day of the month.

month_to_add An optional integer expression that specifies the number of months to add to start_date.

If the month_to_add argument has a value, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then EOMONTH will raise an error.

Return Types

Remarks

The EOMONTH function can remote to SQL Server 2012 (11.x) servers and higher. It cannot be remote to servers with a version lower than SQL Server 2012 (11.x).

Example 1

EOMONTH with explicit datetime type

Example 2

EOMONTH with string parameter and implicit conversion

Example 3

EOMONTH with and without the month_to_add parameter

SWITCHOFFSET

The SWITCHOFFSEET function returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

DATETIMEOFFSETDATETIMEOFFSET Is an expression that can be resolved to a datetimeoffset(n) value.

time_zone Is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.

Return Types

datetimeoffset with the fractional precision of the DATETIMEOFFSET argument.

Remarks

Use SWITCHOFFSET to select a datetimeoffset value into a time zone offset that is different from the time zone offset that was originally stored. SWITCHOFFSET does not update the stored time_zone value.

SWITCHOFFSET can be used to update a datetimeoffset column.

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly. This is because the query optimizer is unable to obtain accurate cardinality estimates for the datetime value. To resolve this problem, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates and will produce a more efficient query plan.

Example

TODATETIMEOFFSET

TODATETIMEOFFSET function returns a datetimeoffset value that is translated from a datetime2 expression.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

Syntax

Arguments

expression Is an expression that resolves to a datetime2 value.

time_zone Is an expression that represents the time zone offset in minutes (if an integer), for example -120, or hours and minutes (if a string), for example '+13:00'. The range is +14 to -14 (in hours). The expression is interpreted in local time for the specified time_zone.

Return Types

datetimeoffset. The fractional precision is the same as the datetime argument.

Example 1

Changing the time zone offset of the current date and time

The following example changes the zone offset of the current date and time to time zone -07:00.

Example 2

Changing the time zone offset in minutes

The following example changes the current time zone to -120 minutes.

Example 3

Adding a 13-hour time zone offset

The following example adds a 13-hour time zone offset to a date and time.

String Functions

1. Overview

The string functions covered in this section perform operations on a string (char or varchar) input value and return a string or numeric value.

ASCII

ASCII (American Standard Code for Information Interchange) returns the ASCII code value of the leftmost character of a character expression.‌

Syntax

ASCII (character_expression) return_type int

Example

SELECT ASCII('A') SELECT ASCII(1)

CHAR

This function converts an int between 0 to 255 to a character value. Outside of this range, the CHAR function will return a NULL value.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

CHAR (integer_expression) return_type char(1)

Arguments

integer_expression

An integer from 0 through 255.

Return Types

char(1)

Example

SELECT CHAR(65)
SELECT CHAR(100)

CHARINDEX

This function searches for one character expression inside another character string. If found, the function will return the starting position of the first expression.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

CHARINDEX ( expressionToFind , expressionString [ , start_location ] )

Arguments

expressionToFind The expression that needs to be found in the ExpressionString

expressionString The string that contains the expression

start_Location Start location from where the search will start

If CHARINDEX does not find expressionToFind within expressionString, CHARINDEX will return 0.

Example 1

Returning the starting position of an expression‌

This example searches for a in the string value.

SELECT CHARINDEX('a', 'this is a beautiful day');

Example 2

Returning the starting position of an expression with an optional start location‌

This example searches for a in the string value starting from 15th position.

SELECT CHARINDEX('a', 'this is a beautiful day', 15);

CONCAT

The CONCAT function concatenates two or more string values one after the other. This function requires at least 2 strings and no more than 254 strings to concatenate.‌

Syntax

CONCAT ( string1, string2 [, stringN ]

Arguments

string A string to concatenate to the other strings.

Return Types

string A string with all the concatenated strings.

Example

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

DIFFERENCE

This function returns an integer value measuring the difference between the SOUNDEX () values of two different character expressions.strings.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

DIFFERENCE ( string , string )

Arguments

string

An alphanumeric expression of character data. string can be a constant, variable, or column.‌

Return Types

int

Example

SELECT SOUNDEX('day'), SOUNDEX('monday'), DIFFERENCE('day', 'monday');

FORMAT

Returns a value formatted with the specified format. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

FORMAT ( value, format )

Arguments

value

Expression of a supported data type to format. For a list of valid types, see the table in the following Remarks section.‌

format

nvarchar format pattern.‌

The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, yyyy (dddd)").

Return Types

nvarchar or null‌

The length of the return value is determined by the format.‌

The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.

Example 1

FORMAT with strings‌

The following example shows formatting date values by specifying a custom format.

SELECT FORMAT( GETDATE(), 'dd/MM/yyyy') AS 'DateTime Format'

Example 2

FORMAT with numerics

The following example shows formatting numeric values by specifying a custom format.

SELECT FORMAT(123456789,'###-##-####') AS 'Numeric Format';

LEFT

Returns the left part of a character string with the specified number of characters.‌

Syntax

LEFT ( string , integer )

Arguments

string

Is an expression of character or binary data. It can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert string.‌

integer

Is a positive integer that specifies how many characters of the string will be returned.

Return Types

Returns a string ‌

Example

The following example returns the two leftmost characters from the string.

SELECT LEFT('abcdefghi, 2) FROM domain.table

LEN

Returns the number of characters of the specified string expression, excluding trailing spaces.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

LEN ( string )

Arguments

string

Is the string expression.‌

Return Types

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.‌

Example

The following example selects the number of characters and the data in string abcde.

SELECT LEN('abcde')

LOWER

Returns a character expression after converting uppercase character data to lowercase.‌

Syntax

LOWER ( string )

Arguments

string

Is an expression of character or binary data.

Return Types

varchar or nvarchar‌

Example

The following example uses the LOWER function.

SELECT LOWER('ABCDE') AS LowerString

LTRIM

Returns a character expression after it removes leading blanks.‌

Syntax

LTRIM ( string )

Arguments

string

Is an expression of character or binary data.‌

Return Types

varchar or nvarchar‌

Example: Using LTRIM

The following example uses LTRIM to remove leading spaces from a string

SELECT LTRIM(' Remove trailing spaces.')

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

PATINDEX ( '%pattern%' , expression )

Arguments

pattern

Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern.

expression

Is an expression, typically a column that is searched for the specified pattern. expression is of the string data type category.‌

Return Types

int or bigint

Example

The following example checks a short character string (this is a great day) for the starting location of the characters eat.

SELECT PATINDEX('%eat%', 'this is a great day')

REPLACE

Replaces all occurrences of a specified string value with another string value.‌

Syntax

REPLACE ( string , string_toBeReplaced , string_replacedBy )

Arguments

string

Is the string expression to be searched.‌

string_toBeReplaced

Is the string to be found in the string.

string_replacedBy

Is the replacement string.

Return Types

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.‌

Example

The following example replaces the string cde in abcdefghi with xyz.

SELECT REPLACE('abcdefghicde','cde','xyz');
GO

REVERSE

Returns the reverse order of a string value.‌

Syntax

REVERSE ( string )

Arguments

string

It is an expression of a string or binary data type.

Return Types

varchar or nvarchar‌

Example

The following example returns the reverse of the sting.

SELECT SELECT reverse('123456789')

RIGHT

Returns the right part of a character string with the specified number of characters.‌

Syntax

RIGHT ( string , integer )

Arguments

string

Is an expression of character or binary data. ‌

integer

Is a positive integer that specifies how many characters of string will be returned.

Return Types

Returns varchar when character_expression is a non-Unicode character data type.‌

Returns nvarchar when character_expression is a Unicode character data type.‌

Example

Using RIGHT with a string

SELECT RIGHT('Good Day', 5)

RTRIM

Returns a character string after truncating all trailing spaces.‌

Syntax

RTRIM ( string )

Arguments

string

Is an expression of character data. ‌

Return Types

varchar or nvarchar‌

Example

The following example takes a string of characters that has spaces at the end of the sentence and returns the text without the spaces at the end of the sentence.

SELECT RTRIM('Removes trailing spaces. ');

SOUNDEX

Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

SOUNDEX ( string )

Arguments

string

Is an alphanumeric expression of character data. SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken.

Return Types

varchar‌

Example

The following example shows the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Raul and Rahul returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.

SELECT SOUNDEX ('Raul'), SOUNDEX ('Rahul');

SPACE

Returns a string of repeated spaces.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

SPACE ( integer_expression )

Arguments

integer_expression

Is a positive integer that indicates the number of spaces.

Return Types

varchar‌

Example

The following example concatenates a comma, two spaces, and the first name of the person.

SELECT 'John' + ',' + SPACE(2) + 'Doe'

STR

Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

STR ( float_expression [ , length [ , decimal ] ] )

Arguments

float_expression

Is an expression of approximate numeric (float) data type with a decimal point.‌

length

Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.‌

decimal

Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.‌

Return Types

varchar‌

Example

The following example converts an expression that is made up of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.

SELECT STR(345.67, 6, 1);
GO

‌STUFF

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.‌

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

STUFF ( string , start , length , replaceWith )

Arguments

string

Is an expression of character data.

start

Is an integer value that specifies the location to start deletion and insertion.

length

Is an integer that specifies the number of characters to delete.

replaceWith

Is an expression of character data.

Return Types

Returns character data if string is one of the supported character data types. Returns binary data if string is one of the supported binary data types.‌

Example

The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO

SUBSTRING

Returns part of a character, binary, text, or image expression in SQL Server.‌

Syntax

SUBSTRING ( expression , start , length )

Arguments

expression

Is a character, binary, text, ntext, or image expression.‌

start

Is an integer or bigint expression that specifies where the returned characters start.

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned.

Return Types

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.S

Example‌

The following example shows how to return only a part of a character string.

SELECT SUBSTRING('Rahul', 1, 1) AS FirstCharOfName

UPPER

Returns a character expression with lowercase character data converted to uppercase.‌

Syntax

UPPER ( string )

Arguments

string

Is an expression of character data.

Return Types

varchar or nvarchar‌

Examples

The following example uses the UPPER function to return the name in uppercase.

SELECT UPPER('rahul')

Mathematical Functions

1. Overview

Mathematical functions perform calculations based on input values provided as parameters to the functions, and return numeric values.

The mathematical functions covered in this section are:

ABS

A mathematical function that returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.)

Syntax

ABS ( numeric_expression )

Arguments

numeric_expression An expression of the exact numeric or approximate numeric data type category.

Return Types

Returns the same type as numeric_expression.

Example

This example shows the results of using the ABS function on three different numbers.

SELECT ABS(-5.2), ABS(0.0), ABS(5.2);

ACOS

A function that returns the angle, in radians, whose cosine is the specified float expression. This is also called arccosine.

Syntax

ACOS ( float_expression )

Arguments

float_expression An expression of either type float or of a type that can implicitly convert to float. Only a value ranging from -1.00 to 1.00 is valid. Values outside this range return NULL and ACOS will report a domain error.

Return Types

float

Example

This example returns the ACOS value of the specified angle.

SELECT 'The ACOS of ' + @number + ' is: ' + CONVERT(varchar, ACOS(@number))

ASIN

A function that returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.

Syntax

ASIN ( float_expression )

Arguments

Return Types

float

Example

This example returns the ASIN value of the specified angle.

SELECT 'The ASIN of ' + @number + ' is: ' + CONVERT(varchar, ASIN(@number))

ATAN

A function that returns the angle, in radians, whose tangent is a specified float expression. This is also called arctangent.

Syntax

ATAN ( float_expression )

Arguments

float_expression An expression of either type float or of a type that implicitly converts to float.

Return Types

float

Example

This example returns the ATAN value of the specified angle.

SELECT 'The ATAN of ' + @number + ' is: ' + CONVERT(varchar, ATAN(@number))

ATN2

Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.

Syntax

ATN2 ( float_expression , float_expression )

Arguments

float_expression An expression of data type float.

Return Types

float

Example

The following example calculates the ATN2 for the specified x and y components.

SELECT 'The ATAN from the x-axis to point (' + @x + ',' + @y + ') is: ' + CONVERT(varchar, ATN2(@y,@x))

CEILING

This function returns the smallest integer greater than, or equal to, the specified numeric expression.

Syntax

CEILING ( numeric_expression )

Arguments

numeric_expression An expression of the exact numeric or approximate numeric data type category. For this function, the bit data type is invalid

Return Types

Return values have the same type as numeric_expression.

Example

This example shows positive numeric, negative numeric, and zero value inputs for the CEILING function.

SELECT CEILING(1.2), CEILING(-1.2), CEILING(0)

COS

A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression.

Syntax

COS ( float_expression )

Arguments

float_expression An expression of type float.

Return Types

float

Example

This example returns the COS value of the specified angle.

SELECT 'The COS of ' + @number + ' is: ' + CONVERT(varchar, COS(@number))

COT

A mathematical function that returns the trigonometric cotangent of the specified angle - in radians - in the specified float expression.

Syntax

COT ( float_expression )

Arguments

float_expression An expression of type float, or of a type that can implicitly convert to float.

Return Types

float

Example

This example returns the COT value for the specific angle.

SELECT 'The COT of ' + @number + ' is: ' + CONVERT(varchar, COT(@number))

DEGREES

This function returns the corresponding angle, in degrees, for an angle specified in radians.

Syntax

DEGREES ( numeric_expression )

Arguments

numeric_expression An expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns a value whose data type matches the data type of numeric_expression.

Example

This example returns the number of degrees in a specified radian.

SELECT 'The number of degrees in ' + @radians + ' radians is: '
       + CONVERT(VARCHAR, DEGREES(@radians))

EXP

Returns the exponential value of the specified float expression.

Syntax

EXP ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Example

The following example uses a compounding interest example to illustrate the use of EXP.

SELECT 'With continuous compounding interest, your principal amount of $'
  + @principal + ' will turn into $' 
  + CONVERT(VARCHAR,@principal * EXP(@years * CAST(@interestRate AS FLOAT)))
  +' after ' + @years + ' years at the interest rate of '
  + CONVERT(VARCHAR,CAST(@interestRate AS FLOAT) * 100) + '%'

FLOOR

Returns the largest integer less than or equal to the specified numeric expression.

Syntax

FLOOR ( numeric_expression )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as numeric_expression.

Example

The following example shows positive numeric, negative numeric, and zero value inputs with the FLOOR function.

SELECT FLOOR(1.2), FLOOR(-1.2), FLOOR(0)

LOG

Returns the natural logarithm of the specified float expression in SQL Server.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

LOG ( float_expression [, base ] )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

base Optional integer argument that sets the base for the logarithm.

Applies to: SQL Server 2012 (11.x) and later

Return Types

float

Remarks

By default, LOG() returns the natural logarithm. Starting with SQL Server 2012 (11.x), you can change the base of the logarithm to another value by using the optional base parameter.

The natural logarithm is the logarithm to the base e, where e is an irrational constant approximately equal to 2.718281828.

The natural logarithm of the exponential of a number is the number itself: LOG( EXP( n ) ) = n. And the exponential of the natural logarithm of a number is the number itself: EXP( LOG( n ) ) = n.

Example

The following example calculates the LOG for a specified number.

SELECT 'The log base ' + @base + ' of ' + @number + ' is: '
  + CONVERT(varchar, LOG(@number,@base))
  
SELECT 'The log of ' + @number + ' is: ' + CONVERT(varchar, LOG(@number))

LOG10

Returns the base-10 logarithm of the specified float expression.

Syntax

LOG10 ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Remarks

The LOG10 and POWER functions are inversely related to one another. For example, 10 ^ LOG10(n) = n.

Example 1

Calculating the base 10 logarithm for a variable.

The following example calculates the LOG10 of the specified number.

SELECT 'The log base 10 of ' + @number + ' is: ' + CONVERT(varchar, LOG10(@number))

Example 2

Calculating the result of raising a base-10 logarithm to a specified power.

The following example returns the result of raising a base-10 logarithm to a specified power.

SELECT POWER (10, LOG10(5))

PI

Returns the constant value of PI.

Syntax

PI ( )

Return Types

float

Example

The following example returns the value of PI.

SELECT PI()

POWER

Returns the value of the specified expression to the specified power.

Syntax

POWER ( float_expression , y )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

y Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

The return type depends on the input type of float_expression:

If the result does not fit in the return type, an arithmetic overflow error occurs.

Example

The following example demonstrates raising a specified number to a specified power.

SELECT @x + ' to the power of ' + @y + ' is: ' + CONVERT(VARCHAR, POWER(@x,@y))

RADIANS

Returns radians when a numeric expression, in degrees, is entered.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

RADIANS ( numeric_expression )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as numeric_expression.

Example

The following example shows the number of radians based on a specified degree.

SELECT @degrees + ' degrees in radians is: ' + CONVERT(VARCHAR, RADIANS(@degrees))

RAND

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Returns a pseudo-random float value from 0 through 1, exclusive.

Syntax

RAND ( [ seed ] )

Arguments

seed Is an integer expression (tinyint, smallint, or int) that gives the seed value. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

Return Types

float

Remarks

Repetitive calls of RAND() with the same seed value return the same results.

For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.

SELECT RAND(100), RAND(), RAND()

Example

SELECT RAND(100), RAND(), RAND(5), RAND(), RAND(100), RAND()

ROUND

Returns a numeric value, rounded to the specified length or precision.

Syntax

ROUND ( numeric_expression , length [ ,function ] )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length Is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

function Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Return Types

Returns the following data types.

Remarks

ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

ROUND returns a rounded numeric_expression, regardless of data type, when length is a negative number.

Example 1

Using ROUND and estimates

The following example shows two expressions that demonstrate by using ROUND the last digit is always an estimate.

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)

Example 2

Using ROUND and rounding approximations

The following example shows rounding and approximations.

SELECT ROUND(123.4545, 2), ROUND(123.45, -2)

Example 3

Using ROUND to truncate

The following example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.

SELECT ROUND(150.75, 0)

SELECT ROUND(150.75, 0, 1)

SIGN

Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Syntax

SIGN ( numeric_expression )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Example

The following example returns the SIGN values of a positive number, negative number, and zero.

SELECT SIGN(5), SIGN(-5), SIGN(0)

SIN

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.

Syntax

SIN ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float, in radians.

Return Types

float

Example

The following example calculates the SIN for a specified angle.

SELECT 'The SIN of ' + @number + ' is: ' + CONVERT(varchar, SIN(@number))

SQRT

Returns the square root of the specified float value.

Syntax

SQRT ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Example

The following example returns the square root of a number.

SELECT 'The square root of ' + @number + ' is: ' + CONVERT(varchar, SQRT(@number))

SQUARE

Returns the square of the specified float value.

Syntax

SQUARE ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Example

The following example returns the square of a specified number.

SELECT @number + ' squared (to the power of 2) is: '
  + CONVERT(varchar, SQUARE(@number))

TAN

Returns the tangent of the input expression.

Syntax

TAN ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float, interpreted as a number of radians.

Return Types

float

Example

The following example returns the tangent of a specified angle.

SELECT 'The TAN of ' + @number + ' is: ' + CONVERT(varchar, TAN(@number))

OGC Methods on Geometry & Geography Instances

1. Overview

Cinchy CQL supports the following on Open Geospatial Consortium (OGC) methods on geometry and geography instances.

Please note that all functions that have been denoted with Geometry in parenthesis are only applicable to OGC methods on geometry instances.

These function are not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations.

The OGC Methods covered in this section are:

STArea

STArea() returns the total surface area of a geometry/geography instance.

Syntax

Return Types

STBoundary (Geometry)

STBoundary() returns the boundary of a geometry instance.

Syntax

Return Types

CQL: geometry

Geometry Example

This example uses STBoundary() on a CurvePolygon instance. STBoundary() returns a CircularString instance:

STBuffer

STBuffer()returns a geometric/geography object that represents the union of all points whose distance from a geometry/geography instance is less than or equal to a specified value.

Syntax

Arguments

distance A value of type float (double in the .NET Framework) specifying the distance from the geometry/geography instance around which to calculate the buffer.

Return Types

CQL: geometry/geography

Remarks

STBuffer() calculates a buffer specifying tolerance = distance * .001 and relative = false.

A negative buffer removes all points within the given distance of the boundary of the geometry/geography instance.

The error between the theoretical and computed buffer is max(tolerance, extents 1.E-7) where tolerance = distance * .001.

Geometry:

When distance > 0 then either a Polygonor MultiPolygoninstance is returned. When distance = 0, then a copy of the calling geometry instance is returned. When distance < 0, then:

When the dimensions of the instance are 0 or 1, an empty GeometryCollectioninstance is returned.
when the dimensions of the instance are 2 or more, a negative buffer is returned.

Geography:

STCrosses() returns 1 if a geometry instance crosses another geometry instance. Returns 0 if it does not.

Syntax

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STCrosses() is invoked.

Return Types

CQL: Yes/No

Remarks

If the spatial reference IDs (SRIDs) of the geometry instances do not match, this method always returns null.

Both conditions must be true for two geometry instances to cross:

The intersection of the two geometry instances results in a geometry whose dimensions are less than the maximum dimension of the source geometry instances.
The intersection set is interior to both source geometry instances.

Geometry Examples

This example uses STCrosses() to test two geometry instances to see if they cross:

STCurveToLine (Geometry)

STCurveToLine()returns a polygonal approximation of a geometry instance that contains circular arc segments.

Syntax

Return Types

CQL: geometry

Remarks

Returns null for uninitialized geometry variables

The polygonal approximation that the method returns depends on the geometry instance used to call the method:

Returns a LineString instance for a CircularStringor CompoundCurveinstance.
Returns a Polygoninstance for a CurvePolygoninstance.
Returns a copy of the geometry instance if that instance is not a CircularString, CompoundCurve, or CurvePolygoninstance.

Any z-coordinate values present in the calling geometry instance are ignored.

Geometry Example

In this example, the SELECT statement uses a LineString instance to call the STCurveToLinemethod. Thus, the method returns a LineStringinstance:

STDifference (Geometry)

STDifference() returns an object that represents the point set from one geometry instance that does not lie within another geometry instance.

Syntax

Arguments

other_instance Another geometry instance to compare against the instance on which STDifference() is invoked.

Return Types

CQL: geometry

Remarks

Returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.

Geometry Example

This example uses STDifference() to compute the difference between two Polygons:

STDisjoint (Geometry)

STDisjoint()returns 1 if a geometry instance is spatially disjoint from another geometry instance, returns 0 if it is not.

Syntax

Arguments

other_instance Another geometry instance to compare against the instance on which STDisjoint() is invoked.

Return Types

CQL: Yes/No

Remarks

If the intersection of the two geometry instances point sets are empty, they are disjoint.

Returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.

Geometry Example

This example uses STDisjoint() to test two geometry instances for spatial disjoint:

STDistance

STDistance() returns the shortest distance between a point in a geometry/geography instance and a point in another geometry/geography instance.

Syntax

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STDistance() is invoked.

Return Types

CQL: Number

Remarks

STDistance() always returns null if the spatial reference IDs (SRIDs) of the geometry/geography instances do not match.

Geometry Example

This example finds the distance between two geometry instances:

Geography Example

This example finds the distance between two geography instances:

STEndpoint (Geometry)

STEndPoint()returns the end point of a geometry instance.

Syntax

Return Types

CQL: geometry

Remarks

Returns null if called on an empty geometry instance.

Geometry Example

STEnvelope (Geometry)

STEnvelope()returns the minimum axis-aligned bounding rectangle of the instance.

Syntax

Return Types

CQL: geometry

Geometry Example

STEquals (Geometry)

STEquals() returns 1 if a geometry instance represents the same point set as another geometry instance, returns 0 if it does not.

Syntax

Arguments

other_instance Another geometry instance to compare against the instance on which STEquals() is invoked.

Return Types

CQL: Yes/No

Remarks

Returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match.

Geometry Example

STExteriorRing (Geometry)

STExteriorRing()returns the exterior ring of a geometry instance that is a Polygon.

Syntax

Return Types

CQL: geometry

Remarks

Returns null if the geometry instance is not a Polygon.

Geometry Example

This example creates a polygon instance and uses STExteriorRing() to return the exterior ring of the polygon as a LineString:

STGeometryN (Geometry)

STGeometryN() returns a specified geometry in a geometry collection.

Syntax

Arguments

expression Is an int expression between 1 and the number of geometry instances in the GeometryCollection.

Return Types

CQL: geometry

Remarks

Returns null if the parameter is larger than the result of STGeometryN() and will throw an ArgumentOutOfRangeExceptionif the expression parameter is less than 1.

Geometry Example

This example creates a MultiPoint GeometryCollectionand uses STGeometryN() to find the second geometry instance of the collection:

STGeometryType (Geometry)

STGeometryType()returns the Open Geospatial Consortium (OGC) type name represented by geometry instance.

Syntax

Return Types

CQL: Text

Remarks

The OGC type names that can be returned by STGeometryType() are Point, LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString, MultiPolygon, and FullGlobe.

Geometry Example

This example creates a Polygon instance and uses STGeometryType() to confirm that it is a Polygon:

STGeomCollFromText (Geometry)

Augmented with any Z (elevation) and M (measure) values carried by the instance, STGeomCollFromText()returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

Arguments

geometrycollection_tagged_text An nvarchar(max) expression that is the WKT representation of the geometry instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry instance you wish to return.

Return Types

CQL: geometry

Remarks

The OGC type of the geometry instance returned by STGeomCollFromText() is set to the corresponding WKT input.

Throws an ArgumentException if the input is not valid.

Geometry Example

This example uses STGeomCollFromText() to create a geometry instance:

STGeomFromText

Augmented with any Z (elevation) and M (measure) values carried by the instance, STGeomFromText()returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

Arguments

inctance_tagged_text An nvarchar(max) expression that is the WKT representation of the geometry/geography instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry/geography instance you wish to return.

Return Types

CQL: geometry/geography

Remarks

The OGC type of the geometry/geography instance returned by STGeomFromText() is set to the corresponding WKT input.

If the input isn't well-formatted, method will throw a FormatException.

Geography:

Throws an ArgumentException if the input contains an antipodal edge.

Geometry Example

This example uses STGeomFromText() to create a geometry instance:

Geography Example

This example uses STGeomCollFromText() to create a geography instance:

STGeomFromWKB

STGeomFromWKB()returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.

Syntax

Arguments

WKB_instance An nvarchar(max) expression that is the WKB representation of the geometry/geography instance to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry/geography instance to return.

Return Types

CQL: geometry/geography

Remarks

The OGC type of the geometry/geography instance returned by STGeomFromWKB() is set to the corresponding WKB input.

If the input isn't well-formatted, method will throw a FormatException.

Geography:

Throws an ArgumentException if the input contains an antipodal edge.

Geometry Example

This example uses STGeomFromWKB() to create a geometry instance:

Geography Example

This example uses STGeomFromWKB() to create a geography instance:

STInteriorRingN (Geometry)

STInteriorRingN() returns the specified interior ring of a Polygongeometry instance.

Syntax

Arguments

expression An int expression between 1 and the number of interior rings in the geometry instance.

Return Types

CQL: geometry

Remarks

Returns null if the geometry instance is not a Polygon.

Geometry Example

This example creates a Polygon instance and uses STInteriorRingN() to return the interior ring of the Polygonas a LineString:

STIntersection

STIntersection() returns an object that represents the points where a geometry/geography instance intersects another geometry/geography instance.

Syntax

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STIntersection() is invoked.

Return Types

CQL: geometry/geography

Remarks

If the spatial reference IDs (SRIDs) of the geometry/geography instances do not match, STIntersection() always returns null.

The result may contain circular arc segments only if the input instances contain them.

Geometry Example

This example uses STIntersection() to compute the intersection of two polygons:

Geography Example

This example uses STIntersection() to compute the intersection of a Polygon and a LineString:

STIntersects

STIntersects() returns 1 if a geometry instance intersects another geometry instance. Returns 0 if it does not.

Syntax

Arguments

other_instance Another geometry/geography instance to compare against the instance on which STIntersects() is invoked.

Return Types

CQL: Yes/No

Remarks

Returns null if the spatial reference IDs (SRIDs) of the geometry/geography instances do not match.

Geometry Example

This example uses STIntersects() to determine if two geometry instances intersect each other:

Geography Example

This example uses STIntersects() to determine whether two geography instances intersect each other:

STIsClosed (Geometry)

STIsClosed()returns 1 if the start and end points of the given geometry instance are the same. Returns 1 for GeometryCollection types if each contained geometry instance is closed. Returns 0 if the instance is not closed.

Syntax

Return Types

CQL: Yes/No

Remarks

Returns 0 if any figures of a geometry instance are points, or if the instance is empty.

All Polygoninstances are considered closed.

Geometry Example

This example creates a LineString instance and uses STIsClosed() to test if the LineString is closed:

STIsEmpty (Geometry)

STIsEmpty()returns 1 if a geometry instance is empty. Returns 0 if a geometry instance is not empty.

Syntax

Return Types

CQL: Yes/No

Geometry Example

This example creates an empty geometry instance and uses STIsEmpty() to test whether the instance is empty:

STIsRing (Geometry)

STIsRing() returns 1 if a geometry instance fulfills the following requirements:

It is a LineStringinstance.
It is closed (for a geometry to be closed, STIsClosed() needs to return 1 when invoked on the instance).
It is simple (for a geometry to be simple, STIsSimple() needs to return 1 when invoked on the instance).
Returns 0 if the LineStringinstance does not meet the requirements.

Syntax

Return Types

CQL: Yes/No

Remarks

Returns null if the instance is not a LineString.

Geometry Example

This example creates a LineString instance and uses STIsRing() to test whether the instance is a ring:

STIsSimple (Geometry)

STIsSimple() returns 1 if a geometry instance is simple, as defined by the Open Geospatial Consortium (OGC). Returns 0 if a geometry instance is not simple.

Syntax

Return Types

CQL: Yes/No

Remarks

To be simple a geometry instance must meet the requirements:

Except at the endpoints, each figure of the instance must not intersect itself.
No two figures of the instance can intersect each other at a point that is not in both of their boundaries.

Geometry Example

This example creates a non-simple LineString instance that intersects itself and uses STIsSimple() to test whether the LineString is simple:

STIsValid (Geometry)

STIsValid()returns true if a geometry instance is well-formed, based on its Open Geospatial Consortium (OGC) type. Returns false if a geometry instance is not well-formed.

Syntax

Return Types

CQL: Yes/No

Remarks

SQL Server produces only valid geometry instances, but allows for the storage and retrieval of invalid instances.

Geometry Example

This example creates a geometry instance and uses STIsValid() to test if the instance is valid:

STLength

STLength()returns the total length of the elements in a geometry/geography instance or the geometry/geography instances within a GeometryCollection.

Syntax

Return Types

CQL: Yes/No

Remarks

Remarks

An empty one-dimensional geometry instance returns 0.

This example creates a Polygon instance and uses STNumInteriorRing() to find how many interior rings the instance has:

STNumPoints (Geometry)

STNumPoints() returns the sum of the number of points in each of the figures in a geometry instance.

Syntax

Return Types

CQL: Number

Remarks

STNumPoints() counts the points (duplicate points are counted) in the description of a geometry instance. If this instance is a collection type, this method returns the sum of the points in each of its elements.

Example

This example creates a LineString instance and uses STNumPoints() to determine how many points were used in the description of the instance:

STOverlaps (Geometry)

STOveralps()returns 1 if a geometry instance overlaps another geometry instance. Returns 0 if it does not.

Syntax

Arguments

other_instance Another geometry instance to compare against the instance on which STOverlaps() is invoked.

Return Types

CQL: Yes/No

Remarks

If the points where the geometry instances intersect are not in the same dimension, STOverlaps() always returns 0.

If the spatial reference IDs (SRIDs) of the geometry instances do not match, STOverlaps() returns null.

Two geometry instances overlap if the region representing their intersection has the same dimension as the instances do and the region does not equal either instance.

Example

This example uses STOverlaps() to test two geometry instances for overlap:

STPointFromText (Geometry)

Only available in SQLServer implementations.

Augmented with any Z (elevation) and M (measure) values carried by the instance, STPointFromText() returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

Arguments

point_tagged_text An nvarchar(max) expression that is the WKT representation of the geometry Point instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry Pointinstance you wish to return.

ReturnTypes

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Example

This example usesSTPointFromText() to create a geometry instance:

STPointFromWKB (Geometry)

STPointFromWKB()returns a geometry Point instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.

Syntax

Arguments

WKB_point A varbinary(max) expression that is the WKB representation of the geometry Point instance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry Point instance you wish to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Example

This example usesSTPointFromWKB() to create a geometry instance:

STPointN (Geometry)

STPointN()returns a specified point in a geometry instance.

Syntax

Arguments

expression An int expression between 1 and the number of points in the geometry instance.

Return Types

CQL: geometry

Remarks

Throws an ArgumentOutOfRangeException, if this method is called with a value less than 1.

Returns null if this method is called with a value greater than the number of points in the instance.

STPointN() returns the point specified by expression, if a geometry instance is user created. (occurs by ordering the points in which they were originally input).

STPointN() returns the point specified by expression, if a geometry instance was constructed by the system (by ordering all the points in the same order they would be output: first by geometry, then by ring within the instance (if appropriate), and then by point within the ring).

Example

This example creates a LineString instance and uses STPointN() to retrieve the second point in the description of the instance:

STPointOnSurface (Geometry)

STPointOnSurface()returns an arbitrary point located within the interior of a geometry instance.

Syntax

Return Types

CQL: geometry

Remarks

If the instance is empty, method returns null.

Example

This example creates a Polygon instance and uses STPointOnSurface() to find a point on the instance:

STPolyFromText (Geometry)

Only available in SQLServer implementations.

Augmented with any Z (elevation) and M (measure) values carried by the instance, STPolyFromText()returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

Syntax

Arguments

polygon_tagged_text An nvarchar(max) expression that is the WKT representation of the geometry Polygoninstance you wish to return.

SRID An int expression representing the spatial reference ID (SRID) of the geometry Polygon instance you wish to return.

Return Types

CQL: geometry

Remarks

If the input isn't well-formatted, method will throw a FormatException.

Example

This example usesSTPolyFromText() to create a geometry instance:

STRelate (Geometry)

STRelate()returns 1 if a geometry instance is related to another geometry instance, otherwise, returns 0. (The relationship between the geometry instances is defined by a Dimensionally Extended 9 Intersection Model (DE-9IM) pattern matrix value)

Syntax

Arguments

other_instance Another geometry instance to compare against the instance on which STRelate() is invoked.

intersection_pattern_matrix Is a string of type nchar(9) encoding acceptable values for the DE-9IM pattern matrix device between the two geometry instances.