5️⃣
Cinchy Platform Documentation
Cinchy v5.8
Cinchy v5.8
  • Data Collaboration Overview
  • Release notes
    • Release notes
      • 5.9 release notes
      • 5.8 Release Notes
      • 5.7 Release Notes
      • 5.6 Release Notes
      • 5.5 Release Notes
      • 5.4 Release Notes
      • 5.3 Release Notes
      • 5.2 Release Notes
      • 5.1 Release Notes
      • 5.0 Release Notes
  • Support
  • Glossary
  • FAQ
  • Deployment guide
    • Deploying Cinchy
      • Plan your deployment
        • Deployment architecture
          • Kubernetes architecture
          • IIS architecture
        • Deployment prerequisites
          • Single Sign-On (SSO) integration
            • Enable TLS 1.2
            • Configure ADFS
            • AD Group Integration
      • Kubernetes
        • Disable your Kubernetes applications
        • Change your file storage configuration
        • Configure AWS IAM for Connections
        • Use Self-Signed SSL Certs (Kubernetes)
        • Deploy the CLI (Kubernetes)
      • IIS
  • Upgrade guide
    • Upgrade Cinchy
      • Cinchy Upgrade Utility
      • Kubernetes upgrades
        • v5.1 (Kubernetes)
        • v5.2 (Kubernetes)
        • v5.3 (Kubernetes)
        • v5.4 (Kubernetes)
        • v5.5 (Kubernetes)
        • v5.6 (Kubernetes)
        • v5.7 (Kubernetes)
        • v5.8 (Kubernetes)
        • Upgrade AWS EKS Kubernetes version
        • Update the Kubernetes Image Registry
        • Upgrade Azure Kubernetes Service (AKS)
      • IIS upgrades
        • v4.21 (IIS)
        • v4.x to v5.x (IIS)
        • v5.1 (IIS)
        • v5.2 (IIS)
        • v5.3 (IIS)
        • v5.4 (IIS)
        • v5.5 (IIS)
        • v5.6 (IIS)
        • v5.7 (IIS)
        • v5.8 (IIS)
      • Upgrading from v4 to v5
  • Guides for using Cinchy
    • User Guide
      • Data Browser overview
      • The Admin panel
      • User preferences
        • Personal access tokens
      • Table features
      • Data management
      • Queries
      • Version management
        • Versioning best practices
      • Commentary
    • Builder Guide
      • Best practices
      • Create tables
        • Attach files
        • Columns
        • Data controls
          • Data entitlements
          • Data erasure
          • Data compression
        • Formatting rules
        • Indexing & partitioning
        • Linking data
        • Table and column GUIDs
        • System tables
      • Delete tables
        • Restore tables, columns, and rows
      • Saved queries
      • CinchyDXD
        • Overview
        • DXD workflow
        • Package the data experience
        • Install the data experience
        • Release package
        • Changelog
        • References
          • Cinchy DXD CLI reference
          • Data Experience Definitions table
          • Data Experience Reference table
      • Multilingual support
      • Integration guides
    • Administrator Guide
    • Additional guides
      • Monitor and Log on Kubernetes
        • Grafana
        • OpenSearch dashboards
          • Set up Alerts
        • Monitor via ArgoCD
      • Maintenance
      • Cinchy Secrets Manager
      • GraphQL (Beta)
      • System properties
      • Enable Data At Rest Encryption (DARE)
      • Application experiences
        • Network map
          • Custom node results
          • Custom results in the Network Map
        • Set up experiences
  • API Guide
    • API overview
      • API authentication
      • API saved queries
      • ExecuteCQL
      • Webhook ingestion
  • CQL
    • Overview
      • CQL examples
      • CQL statements overview
        • Cinchy DML statements
        • Cinchy DDL statements
      • Cinchy supported functions
        • Cinchy functions
        • Cinchy system values
        • Cinchy User Defined Functions (UDFs)
          • Table-valued functions
          • Scalar-valued functions
        • Conversion functions
        • Date and Time types and functions
          • Return System Date and Time values
          • Return Date and Time parts
          • Return Date and Time values from their parts
          • Return Date and Time difference values
          • Modify Date and Time values
          • Validate Date and Time values
        • Logical functions
        • Math functions
        • String functions
        • Geometry and Geography data type and functions
          • OGC methods on Geometry & Geography instances
          • Extended methods on Geometry & Geography instances
        • Full Text Search functions
        • Connections functions
        • JSON functions
    • CQL functions reference list
  • Meta-Forms
    • Introduction
    • Install Meta-Forms
      • Deploy Meta-Forms (Kubernetes)
      • Deploy Meta-Forms (IIS)
    • Forms data types
    • Meta-Forms Builder Guide
      • Create a dynamic meta-form with tables
      • Create a dynamic meta-form example with Form Designer
      • Add links to a form
      • Rich text editing in forms
  • Data syncs
    • Get started with data syncs
    • IIS installation
      • Install Connections
      • Install the Worker/Listener
      • Install the Connections CLI
    • Build data syncs
      • Data sync types
      • Design patterns
      • Sync actions
      • Columns and mappings
        • Calculated column examples
      • Advanced settings
        • Filters
        • Variables
        • Auth requests
        • Request headers
        • Post sync scripts
        • Pagination
      • Batch data sync example
      • Real-time sync example
      • Schedule a data sync
      • Connection functions
    • Data sync sources
      • Cinchy Event Broker/CDC
        • Cinchy Event Broker/CDC XML config example
      • Cinchy Table
        • Cinchy Table XML config example
      • Cinchy Query
        • Cinchy Query XML config example
      • Copper
      • DB2 (query and table)
      • Dynamics 2015
      • Dynamics
      • DynamoDB
      • File-based sources
        • Binary file
        • Delimited file
        • Excel
        • Fixed width file
        • Parquet
      • Kafka Topic
        • Kafka Topic example config
        • Apache AVRO data format
      • LDAP
      • MongoDB collection
        • MongoDB collection source example
      • Mongo event
      • MongoDB collection (Cinchy event)
      • MS SQL Server (query and table)
      • ODBC Query
      • Oracle (query and table)
      • Polling event
        • Polling event example config
      • REST API
      • REST API (Cinchy event)
      • SAP SuccessFactors
      • Salesforce Object (Bulk API)
      • Salesforce platform event
      • Salesforce push topic
      • Snowflake
        • Snowflake source example config
      • SOAP 1.2 web service
      • SOAP 1.2 web service (Cinchy Event Triggered)
    • Data sync destinations
      • Cinchy Table
      • DB2 table
      • Dynamics
      • Kafka Topic
      • MongoDB collection
      • MS SQL Server table
      • Oracle table
      • REST API
      • Salesforce
      • Snowflake table
      • SOAP 1.2 web service
    • Real-time sync stream sources
      • The Listener Config table
      • Cinchy Event Broker/CDC
      • Data Polling
      • Kafka Topic
      • MongoDB
      • Salesforce Push Topic
      • Salesforce Platform Event
    • CLI commands list
    • Troubleshooting
  • Other Resources
    • Angular SDK
    • JavaScript SQK
Powered by GitBook
On this page
  • Overview
  • DATEADD
  • Syntax
  • Arguments
  • Return types
  • EOMONTH
  • Syntax
  • Arguments
  • Return Types
  • SWITCHOFFSET
  • Syntax
  • Arguments
  • Return Types
  • TODATETIMEOFFSET
  • Arguments
  • Return Types
  1. CQL
  2. Overview
  3. Cinchy supported functions
  4. Date and Time types and functions

Modify Date and Time values

PreviousReturn Date and Time difference valuesNextValidate Date and Time values

Last updated 1 year ago

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 isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.

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.

datepart

year

quarter

month

dayofyear

day

week

weekday

hour

minute

second

millisecond

microsecond

nanosecond

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 won't 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.

This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.

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 can't 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's changed from the stored time zone offset to a specified new time zone offset.

This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.

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's different from the time zone offset that was originally stored. SWITCHOFFSET doesn't 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's translated from a datetime2 expression.

This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.

### 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
DATEADD
​EOMONTH
SWITCHOFFSET
TODATETIMEOFFSET​