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

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)