Return Date and Time Parts
1. Overview
The return date and time part functions covered in this section are:
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
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
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
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
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
Example 2: Returns Day the Day Part of the Date
Example 3: Returns the year Part of the Date
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
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
Example 1:
This returns 30
- the number of the day itself
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.
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
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
Example 1:
The following statement returns 4
. This is the number of the month.
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.
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
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
Example 1:
The following statement returns 2020
. This is the number of the year.
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.
Last updated