1. Overview

The Cinchy system values covered in this section:

• @cinchy_row_id

@cinchy_row_id

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

Syntax

select @cinchy_row_id

Examples

INSERT INTO [Contacts].[People] ([First Name],[Last Name])
VALUES ('John','Smith')

select @cinchy_row_id

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).

1. Overview

Cinchy Supported Functions covers the following topics:

• ​

• ​

• ​

• Scalar Functions

Cinchy Functions and Cinchy User Defined Functions (UDFs) are used in conjunction with Cinchy and statements.

Cinchy System Values are specific to the Cinchy platform, similar to Cinchy Functions.

Cinchy User Defined Functions can be used for more customizable logic to accommodate your business requirements.

Scalar functions operate on a single value and return a single value.

​

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 Function Categories

  • ​

  • ​

  • ​

Date and Time Data Types

​

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.

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.

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):

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.

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.

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

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

Sample Query

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

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):

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:

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.

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

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

1. Overview

The validate date and time value function covered in this section is:

• ​ISDATE​

ISDATE

ISDATE checks an expression to see if it is correct.

It will return 1 if the expression is a valid date, time, or datetime value; otherwise, it will return 0. ISDATE will also return 0 if the expression is a datetime2 value.

Syntax

ISDATE ( expression )

Arguments

expression Is a character string or expression that can be converted to a character string. The expression must be less than 4,000 characters. Date and time data types, except datetime and smalldatetime, are not allowed as the argument for ISDATE.

Return Types

int

Remarks

ISDATE is deterministic only when used with the CONVERT function, if the CONVERT style parameter is specified, and style is not equal to 0, 100, 9, or 109.

The return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and Configure the default language Server Configuration Option.

Example

Using ISDATE to Test Valid datetime Expression

IF ISDATE('2009-05-12 10:19:41.177') = 1
     SELECT 'VALID'
ELSE
     SELECT 'INVALID'

1. Overview

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

• DATEFROMPARTS

• DATETIME2FROMPARTS

• DATETIMEFROMPARTS

• DATETIMEOFFSETFROMPARTS

• SMALLDATETIMEFROMPARTS

• TIMEFROMPARTS

DATEFROMPARTS

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

Syntax

DATEFROMPARTS ( year, month, day )

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

date

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

SELECT DATEFROMPARTS(2010, 12, 31) AS Result

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.

Syntax

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

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

datetime2( precision )

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

SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;

Example 2: With Fractions of a Second

1. When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.

2. When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.

3. 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.

SELECT DATETIME2FROMPARTS(2011, 8, 15, 14, 23, 44, 5, 1)

DATETIMEFROMPARTS

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

Syntax

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

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

datetime

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

SELECT DATETIMEFROMPARTS( 2010, 12, 31, 23, 59, 59, 0 ) AS Result

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.

Syntax

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

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

datetimeoffset( precision )

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

SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Result;

Example 2: With Fractions of a Second

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

1. When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.

2. When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.

3. 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.

SELECT DATETIMEOFFSETFROMPARTS( 2011, 8, 15, 14, 30, 00, 5, 12, 30, 1 )

SMALLDATETIMEFROMPARTS

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

Syntax

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

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

smalldatetime

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

SELECT SMALLDATETIMEFROMPARTS( 2010, 12, 31, 23, 59 ) AS Result

TIMEFROMPARTS

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

Syntax

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

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

time( precision )

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

SELECT TIMEFROMPARTS( 23, 59, 59, 0, 0 ) AS Result

Example 2: With Fractions of a Second

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

1. 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.

2. 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.

3. 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.

SELECT TIMEFROMPARTS ( 14, 23, 44, 5, 1 )

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.

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.

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.

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'

1. Overview

The logical functions covered in this section are:

• ​CHOOSE​

• ​IIF​

CHOOSE

The CHOOSE function returns an item at the specified index from a list of values in Cinchy.

Syntax

CHOOSE ( index, val_1, val_2 [, val_n ] )

Arguments

index Is an integer expression that represents a 1-based index into the list of the items following it.

If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null.

Return Types

Returns the data type with the highest precedence from the set of types passed to the function.

Remarks

CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

Example 1

Simple CHOOSE example

SELECT CHOOSE( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result

Example 2

Simple CHOOSE example based on column

SELECT
    [Category ID],
    CHOOSE ([Category ID], 'A', 'B', 'C', 'D', 'E') AS Expression1
FROM
    [Production].[Product Category]

Example 3

CHOOSE in combination with MONTH

The following example returns the season in which a user was added to Cinchy. The MONTH function is used to return the month value from the column HireDate.

SELECT
    [Display Name],
    [Created],
    CHOOSE(
        MONTH([Created]),
        'Winter',
        'Winter',
        'Spring',
        'Spring',
        'Spring',
        'Summer',
        'Summer',
        'Summer',
        'Autumn',
        'Autumn',
        'Autumn',
        'Winter'
    ) AS Quarter
FROM
    [Cinchy].[Users]
WHERE
    [Deleted] IS NULL
    AND YEAR([Created]) > 2005
ORDER BY
    YEAR([Created])

IIF

IFF returns one of two values which is depending on if the Boolean expression evaluates TRUE or FALSE in the Cinchy.

Syntax

IIF ( condition, true_value, false_value )

Arguments

boolean_expression A valid Boolean expression.

If this argument is not a Boolean expression, then a syntax error is raised.

true_value Value to return if boolean_expression evaluates to true.

false_value Value to return if boolean_expression evaluates to false.

Return Types

Returns the data type with the highest precedence from the types in true_value and false_value.

Remarks

IIF is a second version of writing a CASE expression. It evaluates the Boolean expression which was passed as the first argument and then returns either TRUE or FALSE based on the result of the evaluation. Thetrue_valueis returned if the Boolean expression is TRUE, and thefalse_valueis returned if the Boolean expression is FALSE or unknown.

Example

Return 5 if the condition is TRUE, or 10 if the condition is FALSE:

DECLARE @Value CHAR(1) = 'B'
SELECT IIF(@Value = 'A',5,10)

1. Overview

With Cinchy, you can now easily create spatial tables with geometry and geography data sets, and blend this with other sources to unlock more intelligence from your fabric.

This data can be leveraged to support an endless number of use-cases that rely on location and mapping data to help your teams make the most informed decisions.

The geometry and geography functions covered in this section are categorized as follows:

• OGC Methods on Geometry & Geography Instances

• Extended Methods on Geometry & Geography Instances

1. Overview

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

• DATEDIFF

• DATEDIFF_BIG

DATEDIFF

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

Syntax

DATEDIFF ( datepart , startdate , enddate )

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

int

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.

SELECT
    DATEDIFF(day, [Created], [Modified]) AS 'Duration'
FROM
    [Cinchy].[Tables]
WHERE
	[Deleted] IS NULL

Example 2

Specifying user-defined variables for startdate and enddate

DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722'
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722'
SELECT DATEDIFF(day, @startdate, @enddate)

Example 3: Specifying scalar system functions for startdate and enddate

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME())

DATEDIFF_BIG

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

Syntax

DATEDIFF_BIG ( datepart , startdate , enddate )

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

Signed bigint

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.

SELECT
    DATEDIFF_BIG(day, [Created], [Modified]) AS 'Duration'
FROM
    [Cinchy].[Tables]
WHERE
	[Deleted] IS NULL

1. Overview

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

• ​DATEADD

• ​EOMONTH

• ​SWITCHOFFSET

• ​TODATETIMEOFFSET​

DATEADD

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

Syntax

DATEADD (datepart , number , date )

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

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'

SELECT 'year', DATEADD(year,1,@datetime2)
UNION ALL
SELECT 'quarter',DATEADD(quarter,1,@datetime2)
UNION ALL
SELECT 'month',DATEADD(month,1,@datetime2)
UNION ALL
SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2)
UNION ALL
SELECT 'day',DATEADD(day,1,@datetime2)
UNION ALL
SELECT 'week',DATEADD(week,1,@datetime2)
UNION ALL
SELECT 'weekday',DATEADD(weekday,1,@datetime2)
UNION ALL
SELECT 'hour',DATEADD(hour,1,@datetime2)
UNION ALL
SELECT 'minute',DATEADD(minute,1,@datetime2)
UNION ALL
SELECT 'second',DATEADD(second,1,@datetime2)
UNION ALL
SELECT 'millisecond',DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT 'microsecond',DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2)

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:

DECLARE @datetime2 datetime2
SET @datetime2 = '2007-01-01 01:01:01.1111111'

--Statement                                           Result
--------------------------------------------------------------------------
SELECT DATEADD(quarter,4,@datetime2)         --2008-01-01 01:01:01.1111111
SELECT DATEADD(month,13,@datetime2)          --2008-02-01 01:01:01.1111111
SELECT DATEADD(dayofyear,365,@datetime2)     --2008-01-01 01:01:01.1111111
SELECT DATEADD(day,365,@datetime2)           --2008-01-01 01:01:01.1111111
SELECT DATEADD(week,5,@datetime2)            --2007-02-05 01:01:01.1111111
SELECT DATEADD(weekday,31,@datetime2)        --2007-02-01 01:01:01.1111111
SELECT DATEADD(hour,23,@datetime2)           --2007-01-02 00:01:01.1111111
SELECT DATEADD(minute,59,@datetime2)         --2007-01-01 02:00:01.1111111
SELECT DATEADD(second,59,@datetime2)         --2007-01-01 01:02:00.1111111
SELECT DATEADD(millisecond,1,@datetime2)     --2007-01-01 01:01:01.1121111

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:

SELECT
    [Order Number],
    [Order Date],
    DATEADD(day, 2, [Order Date]) AS [Promised Ship Date]
FROM
    [Sales].[Order Header]
WHERE
    [Deleted] IS NULL

EOMONTH

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

Syntax

EOMONTH ( start_date [, month_to_add ] )

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

date

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

DECLARE @date DATETIME = '12/1/2011'
SELECT EOMONTH( @date ) AS Result

Example 2

EOMONTH with string parameter and implicit conversion

DECLARE @date VARCHAR(255) = '12/1/2011'
SELECT EOMONTH( @date ) AS Result

Example 3

EOMONTH with and without the month_to_add parameter

DECLARE @date DATETIME = GETDATE()

SELECT
    EOMONTH ( @date ) AS 'This Month',
    EOMONTH ( @date, 1 ) AS 'Next Month',
    EOMONTH ( @date, -1 ) AS 'Last Month'

SWITCHOFFSET

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

Syntax

SWITCHOFFSET ( DATETIMEOFFSET, time_zone )

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

SELECT SWITCHOFFSET(ColDatetimeoffset, '-08:00')

TODATETIMEOFFSET

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

Syntax

TODATETIMEOFFSET ( expression , time_zone )

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.

DECLARE @todaysDateTime datetime2
SET @todaysDateTime = GETDATE()

SELECT TODATETIMEOFFSET (@todaysDateTime, '-07:00')

-- RETURNS 2019-04-22 16:23:51.7666667 -07:00

Example 2

Changing the time zone offset in minutes

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

SELECT TODATETIMEOFFSET(SYSDATETIME(), -120)

-- RETURNS: 2019-04-22 11:39:21.6986813 -02:00

Example 3

Adding a 13-hour time zone offset

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

SELECT TODATETIMEOFFSET(SYSDATETIME(), '+13:00')

-- RETURNS: 2019-04-22 11:39:29.0339301 +13:00

1. Overview

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

• DATENAME

• DATEPART

• DAY

• MONTH

• YEAR

DATENAME

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

Syntax

DATENAME ( datepart , date )

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

nvarchar

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

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

Syntax

DATEPART ( datepart , date )

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

int

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

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0)

-- Returns: 1900 1 1

Example 2: Returns Day the Day Part of the Date

SELECT TOP(1) DATEPART(day,[Modified])
FROM [Cinchy].[Domains]
WHERE [Deleted] IS NULL

-- Returns: 20

Example 3: Returns the year Part of the Date

SELECT TOP(1) DATEPART(year,[Modified])
FROM [Cinchy].[Domains]
WHERE [Deleted] IS NULL

-- Returns: 2020

DAY

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

Syntax

DAY ( date )

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

int

Example 1:

This returns 30 - the number of the day itself

SELECT DAY('2015-04-30 01:01:01.1234567')

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.

SELECT YEAR(0), MONTH(0), DAY(0)

MONTH

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

Syntax

MONTH ( date )

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

int

Example 1:

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

SELECT MONTH('2007-04-30T01:01:01.1234567 -07:00')

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.

SELECT YEAR(0), MONTH(0), DAY(0)

YEAR

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

Syntax

YEAR ( date )

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

int

Example 1:

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

SELECT YEAR('2020-04-30T01:01:01.1234567-07:00')

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.

SELECT YEAR(0), MONTH(0), DAY(0)

1. Overview

Cinchy CQL supports a number of extended methods on Open Geospatial Consortium (OGC) methods on geometry and geography instances.Please note that all functions that have been denoted with Geography in parentheses are only applicable to OGC methods on geometry instances.

The extended Methods covered in this section are:

IsValidDetailed (Geometry)

IsValidDetailed()returns a message that can help to identify problems with a spatial object that is not valid.

Only the first error is returned, when the object is not valid. When the object is valid, a value of 24400 is returned.

Syntax

.IsValidDetailed()  

Return Types

CQL: Text

Remarks

The following table contains possible return values:

Geometry Example

This example of an invalid spatial object shows how the IsValidDetailed() methods behaves:

DECLARE @p GEOMETRY = 'Polygon((2 2, 4 4, 4 2, 2 4, 2 2))'  
SELECT @p.IsValidDetailed()  
--Returns: 24404: Not valid because polygon ring (1) intersects itself or some other ring.

MakeValid (Geometry)

MakeValid()converts an invalid geometry instance into a geometry instance with a valid Open Geospatial Consortium (OGC) type.

Syntax

.MakeValid ()  

Return Types

CQL: geometry

Remarks

This method may cause a change in the type of the geometry instance, as well as cause the points of a geometry instance to shift slightly.

Geometry Example

This example creates an invalid LineString instance that overlaps itself and uses MakeValid() to make this instance valid:

DECLARE @g geometry;  
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 1 1, 1 0, 1 1, 2 2)', 0);  
SET @g = @g.MakeValid(); 

Reduce (Geometry)

By running the Douglas-Peucker algorithm on the instance with the given tolerance, Reduce()returns an approximation of the given geometry instance produced.

Syntax

.Reduce ( tolerance )  

Arguments

tolerance The tolerance (type float) to input for the approximation algorithm.

Return Types

CQL: geometry

Remarks

This algorithm operates independently on each geometry contained in the instance, for collection types.

Does not modify Pointinstances.

For CircularStringinstances,Reduce() returns a LineString, CircularString, or CompoundCurve instance.

For CompoundCurveinstances,Reduce() returns either a CompoundCurveor LineStringinstance.

On Polygoninstances, the approximation algorithm is applied independently to each ring. If the returned Polygoninstance is not valid, Reduce() will produce a FormatException.

When a circular arc segment is found, the approximation algorithm checks whether the arc can be approximated by its chord within half the given tolerance. Chords meeting this criteria have the circular arc replaced in the calculations by the chord. If a chord doesn't meet this criteria, then the circular arc is kept and the approximation algorithm is applied to the remaining segments.

Geometry Example

This example creates a LineString instance and uses Reduce() to simplify the instance:

DECLARE @g geometry;  
SET @g = geometry::STGeomFromText('LINESTRING(0 0, 0 1, 1 0, 2 1, 3 0, 4 1)', 0);  
SELECT @g.Reduce(.75).ToString();  

ShortestLineTo (Geometry)

ShortestLineTo()returns a LineStringinstance (which is the distance between the two geometry instances) with two points that represent the shortest distance between the two geometry instances.

Syntax

.ShortestLineTo ( other_instance )  

Arguments

other_instance Specifies the second geometry instance that the calling geometry instance is trying to determine the shortest distance to.

Return Types

CQL: geometry

Remarks

Returns a LineStringinstance with endpoints lying on the borders of the two non-intersecting geometry instances being compared.

The length of the LineStringreturned equals the shortest distance between the two geometry instances.

An empty LineStringinstance is returned when the two geometry instances intersect each other.

Geometry Example

This example returns the LineString instance connecting the two points, by finding the shortest distance between a CircularString instance and a LineString instance:

 DECLARE @g1 geometry = 'CIRCULARSTRING(0 0, 1 2.1082, 3 6.3246, 0 7, -3 6.3246, -1 2.1082, 0 0)';  
 DECLARE @g2 geometry = 'LINESTRING(-4 7, 7 10, 3 7)';  
 SELECT @g1.ShortestLineTo(@g2).ToString();

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

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.

ACOS

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

Syntax

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.

ASIN

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

Syntax

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 ASIN will report a domain error.

Return Types

float

Example

This example returns the ASIN value of the specified angle.

ATAN

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

Syntax

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.

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

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.

CEILING

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

Syntax

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.

COS

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

Syntax

Arguments

float_expression An expression of type float.

Return Types

float

Example

This example returns the COS value of the specified angle.

COT

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

Syntax

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.

DEGREES

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

Syntax

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.

EXP

Returns the exponential value of the specified float expression.

Syntax

Arguments

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

Return Types