Return Date and Time Parts

Overview

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

• DATENAME
• DATEPART
• DAY
• MONTH
• YEAR

DATENAME

caution

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.

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

Syntax

DATENAME ( datepart , date )

Return types

nvarchar

Arguments

Argument	Description	Example
date	An expression that can resolve to one of the following data types: date, datetime, datetimeoffset, datetime2, smalldatetime, time. Use four-digit years to avoid ambiguity issues.	2007-10-30 12:15:32.1234567 +05:10
datepart	The specific part of the date argument that DATENAME will return.	day

This table lists all valid datepart arguments.

datepart
year
quarter
month
dayofyear
day
week
weekday
hour
minute
second
millisecond
microsecond
nanosecond
TZoffset
ISO_WEEK

Remarks

Use DATENAME in the following clauses:

• GROUP BY
• HAVING
• ORDER BY
• SELECT
• WHERE

Examples

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

DATEPART

caution

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.

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

Syntax

DATEPART ( datepart , date )

Arguments

Argument	Description	Example
date	An expression that can resolve to one of the following data types: date, datetime, datetimeoffset, datetime2, smalldatetime, time. Use four-digit years to avoid ambiguity issues.	2007-10-30 12:15:32.1234567 +05:10
datepart	The specific part of the date argument for which DATEPART will return an integer.	day

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 returns an integer that represents the day (day of the month) of the specified date.

Syntax

DAY ( date )

Arguments

Argument	Description	Example
date	An expression that can resolve to one of the following data types: date, datetime, datetimeoffset, datetime2, smalldatetime, time. Use four-digit years to avoid ambiguity issues.	2015-04-30 01:01:01.1234567

Return types

int

Example

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

MONTH

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

Syntax

MONTH ( date )

Return types

int

Arguments

Argument	Description	Example
date	An expression that can resolve to one of the following data types: date, datetime, datetimeoffset, datetime2, smalldatetime, time. Use four-digit years to avoid ambiguity issues.	2007-04-30

Example

SELECT MONTH('2007-04-30')

YEAR

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

Syntax

YEAR ( date )

Return Types

int

Arguments

Argument	Description	Example
date	An expression that can resolve to one of the following data types: date, datetime, datetimeoffset, datetime2, smalldatetime, time. Use four-digit years to avoid ambiguity issues.	2020-04-30T01:01:01.1234567-07:00

Example

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