Return Date and Time Values From Their Parts

1. Overview

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

DATEFROMPARTS

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

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.

Syntax 

DATEFROMPARTS ( year, month, day )

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month, from 1 to 12.

day An integer expression that specifies a day.

Return Types 

date

Remarks

DATEFROMPARTS returns a date value, with the date portion set to the specified year, month and day, and the time portion set to the default. For invalid arguments, DATEFROMPARTS will raise an error. DATEFROMPARTS returns null if at least one required argument has a null value.

Example

SELECT DATEFROMPARTS(2010, 12, 31) AS Result

DATETIME2FROMPARTS

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

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.

Syntax 

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

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies the hours.

minute An integer expression that specifies the minutes.

seconds An integer expression that specifies the seconds.

fractions An integer expression that specifies a fractional seconds value.

percision An integer expression that specifies the precision of the datetime2 value that DATETIME2FROMPARTS will return.

Return Types 

datetime2( precision )

Remarks

DATETIME2FROMPARTS returns a fully initialized datetime2 value. DATETIME2FROMPARTS will raise an error if at least one required argument has an invalid value. DATETIME2FROMPARTS returns null if at least one required argument has a null value. However, if the precision argument has a null value, DATETIME2FROMPARTS will raise an error.

The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIME2FROMPARTS will raise an error.

Example 1: Without Fractions of a Second

SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;

Example 2: With Fractions of a Second

  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

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

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.

Syntax 

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

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies hours.

minute An integer expression that specifies minutes.

seconds An integer expression that specifies seconds.

milliseconds An integer expression that specifies milliseconds.

Return Types 

datetime

Remarks

DATETIMEFROMPARTS returns a fully initialized datetime value. DATETIMEFROMPARTS will raise an error if at least one required argument has an invalid value. DATETIMEFROMPARTS returns null if at least one required argument has a null value.

Example

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

DATETIMEOFFSETFROMPARTS

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.

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.

Syntax 

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

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies hours.

minute An integer expression that specifies minutes.

seconds An integer expression that specifies seconds.

frcations An integer expression that specifies a fractional seconds value.

hour_offset An integer expression that specifies the hour portion of the time zone offset.

minute_offset An integer expression that specifies the minute portion of the time zone offset.

precision An integer literal value that specifies the precision of the datetimeoffset value that DATETIMEOFFSETFROMPARTS will return.

Return Types 

datetimeoffset( precision )

Remarks

DATETIMEOFFSETFROMPARTS returns a fully initialized datetimeoffset data type. The offset arguments represent the time zone offset. For omitted offset arguments, DATETIMEOFFSETFROMPARTS assumes a time zone offset of 00:00 - in other words, no time zone offset. For specified offset arguments, DATETIMEOFFSETFROMPARTS expects values for both arguments, and both values positive or negative. If minute_offset has a value and hour_offset has no value, DATETIMEOFFSETFROMPARTS will raise an error. DATETIMEOFFSETFROMPARTS will raise an error if the other arguments have invalid values. If at least one required arguments have a NULL value, then DATETIMEOFFSETFROMPARTS will return NULL. However, if the precision argument has a NULL value, then DATETIMEOFFSETFROMPARTS will raise an error.

The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIMEOFFSETFROMPARTS will raise an error.

Example 1: Without Fractions of a Second

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

Example 2: With Fractions of a Second

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.

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.

Syntax 

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

Arguments

year Integer expression specifying a year.

month Integer expression specifying a month.

day Integer expression specifying a day.

hour Integer expression specifying hours.

minute Integer expression specifying minutes.

Return Types 

smalldatetime

Remarks

This function acts as a constructor for a fully initialized smalldatetime value. If the arguments are not valid, then an error is thrown. If required arguments are null, then null is returned.

Example

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

TIMEFROMPARTS

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

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.

Syntax 

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

Arguments

hour Integer expression specifying hours.

minute Integer expression specifying minutes.

seconds Integer expression specifying seconds.

fractions Integer expression specifying fractions.

precision Integer literal specifying the precision of the time value to be returned.

Return Types 

time( precision )

Remarks

TIMEROMPARTS returns a fully initialized time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

Example 1: Without Fractions of a Second

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

Example 2: With Fractions of a Second

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 )
PreviousReturn Date and Time PartsNextReturn Date and Time Difference Values

Last updated