Cinchy Platform Documentation
Cinchy v5.7
Cinchy v5.7
  • Data Collaboration Overview
  • Release notes
    • 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)
        • 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)
      • 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
        • Build the data experience
        • Package the data experience
        • Install the data experience
        • Update the data experience
        • Repackage the data experience
        • Reinstall the data experience
      • 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)
      • MDQE
      • 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 master 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
      • Prerequisites
      • Install Connections
      • Install the Worker/Listener
      • Install the CLI and the Maintenance 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
    • CLI commands list
    • Troubleshooting
    • 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
    • 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
  • Other Resources
    • Angular SDK
    • JavaScript SQK
Powered by GitBook
On this page
  • Overview
  • DATENAME
  • DATEPART
  • Syntax
  • Arguments
  • Return type
  • Remarks
  • DAY
  • Syntax
  • Arguments
  • Return types
  • MONTH
  • Syntax
  • Arguments
  • Return types
  • YEAR

Was this helpful?

Export as PDF
  1. CQL
  2. Overview
  3. Cinchy supported functions
  4. Date and Time types and functions

Return Date and Time parts

PreviousReturn System Date and Time valuesNextReturn Date and Time values from their parts

Last updated 1 year ago

Was this helpful?

Overview

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

DATENAME

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

datepart

year

quarter

month

dayofyear

day

week

weekday

hour

minute

second

millisecond

microsecond

nanosecond

TZoffset

ISO_WEEK

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

Return value

year

2007

quarter

4

month

October

dayofyear

303

day

30

week

44

weekday

Tuesday

hour

12

minute

15

second

32

millisecond

123

microsecond

123456

nanosecond

123456700

TZoffset

+05:10

ISO_WEEK

44

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 doesn't 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

This example returns the Base Year.

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

-- Returns: 1900 1 1

Example 2

This example returns the Day part of the Date.

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

-- Returns: 20

Example 3

This example 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)

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 .

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 .

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 .

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 .

DATENAME
DATEPART
DAY
MONTH
YEAR
the CQL functions reference page
the CQL functions reference page
the CQL functions reference page
the CQL functions reference page
the CQL functions reference page