Return Date and Time Values From Their Parts

Overview

The following return date and time values from their parts functions covered in this section are:

• DATEFROMPARTS
• DATETIME2FROMPARTS
• DATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• SMALLDATETIMEFROMPARTS
• TIMEFROMPARTS

DATEFROMPARTS

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.

DATEFROMPARTS returns a date value that maps to the specified year, month, and day values.

Syntax

DATEFROMPARTS ( year, month, day )

Arguments

Argument	Description	Example
year	An integer that specifies a year.	2024
month	An integer, between 1-12, that specifies a month.	10
day	An integer that specifies a day.	9

Return types

date

Example

SELECT DATEFROMPARTS(2024, 10, 9)

DATETIME2FROMPARTS

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.

DATETIME2FROMPARTS function returns a datetime2 value for the specified date and time arguments. The returned value has a precision specified by the precision argument.

Syntax

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

Arguments

Argument	Description	Example
year	An integer that specifies a year.	2010
month	An integer, between 1-12, that specifies a month.	12
day	An integer that specifies a day.	31
hour	An integer expression that specifies the hours.	23
minute	An integer expression that specifies the minutes.	59
seconds	An integer expression that specifies the seconds.	59
fractions	An integer expression that specifies a fractional seconds value.	0
precision	An integer expression that specifies the precision of the datetime2 value that DATETIME2FROMPARTS will return.	0

Return types

datetime2( precision )

Example 1

SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 )

Example 2

1. When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.
2. When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.
3. When fractions have a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SELECT DATETIME2FROMPARTS(2011, 8, 15, 14, 23, 44, 5, 1)

DATETIMEFROMPARTS

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.

DATETIMEFROMPARTS function returns a datetime value for the specified date and time arguments.

Syntax

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Arguments

Argument	Description	Example
year	An integer that specifies a year.	2010
month	An integer, between 1-12, that specifies a month.	12
day	An integer that specifies a day.	31
hour	An integer expression that specifies the hours.	23
minute	An integer expression that specifies the minutes.	59
seconds	An integer expression that specifies the seconds.	59
milliseconds	An integer expression that specifies milliseconds.	0

Return types

datetime

Example

SELECT DATETIMEFROMPARTS( 2010, 12, 31, 23, 59, 59, 0 )

DATETIMEOFFSETFROMPARTS

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.

Returns a datetimeoffset value for the specified date and time arguments. The returned value has a precision specified by the precision argument and an offset as specified by the offset arguments.

Syntax

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

Arguments

Argument	Description	Example
year	An integer that specifies a year.	2010
month	An integer, between 1-12, that specifies a month.	12
day	An integer that specifies a day.	31
hour	An integer expression that specifies the hours.	14
minute	An integer expression that specifies the minutes.	23
seconds	An integer expression that specifies the seconds.	23
fractions	An integer expression that specifies a fractional seconds value.	0
hour_offset	An integer expression that specifies the hour portion of the time zone offset.	12
minute_offset	An integer expression that specifies the minute portion of the time zone offset.	0
precision	An integer literal value that specifies the precision of the datetimeoffset value that DATETIMEOFFSETFROMPARTS will return.	7

Return types

datetimeoffset( precision )

Example 1

SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 )

Example 2

This example shows the use of the fractions and precision parameters:

1. When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.
2. When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.
3. When fractions have a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SELECT DATETIMEOFFSETFROMPARTS( 2011, 8, 15, 14, 30, 00, 5, 12, 30, 1 )

SMALLDATETIMEFROMPARTS

SMALLDATETIMEFROMPARTS returns a smalldatetime value for the specified date and time.

Syntax

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Return types

smalldatetime

Arguments

Argument	Description	Example
year	An integer that specifies a year.	2010
month	An integer, between 1-12, that specifies a month.	12
day	An integer that specifies a day.	31
hour	An integer expression that specifies the hours.	23
minute	An integer expression that specifies the minutes.	59

Example

SELECT SMALLDATETIMEFROMPARTS( 2010, 12, 31, 23, 59 )

TIMEFROMPARTS

TIMEFROMPARTS returns a time value for the specified time and with the specified precision.

Syntax

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Arguments

Argument	Description	Example
hour	An integer expression that specifies the hours.	23
minute	An integer expression that specifies the minutes.	59
seconds	Integer expression specifying seconds.	59
fractions	Integer expression specifying fractions.	0
precision	Integer literal specifying the precision of the time value to be returned.	0

Return types

time( precision )

Example 1

SELECT TIMEFROMPARTS( 23, 59, 59, 0, 0 )

Example 2

The following example demonstrates the use of the fractions and precision parameters:

1. When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.
2. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.
3. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SELECT TIMEFROMPARTS ( 14, 23, 44, 5, 1 )