Return Date and Time Parts

1. Overview

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

• DATENAME

• DATEPART

• DAY

• MONTH

• YEAR

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

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

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.

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

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.

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

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.

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

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.

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)