Datetimefromparts SQL is a function used in SQL programming to create a datetime value using individual values for year, month, day, hour, minute, and second. It is an essential tool for SQL programmers as it simplifies the process of creating datetime values from its individual parts.
Syntax of datetimefromparts SQL
Datetimefromparts SQL is a function in SQL that returns a date value from the specified parts such as year, month, and day. This function is usually used when there’s a need to build a date string from different parts of a date.
The syntax for using datetimefromparts SQL is:
| DATEFROMPARTS ( | year, | month, | day) |
Where:
- year: The year value in four digits
- month: The month value (1-12)
- day: The day value (1-31)
These parameters are required for the function to work, and if one of the values is missing or provided incorrectly, it will result in an error.
Example of how to use datetimefromparts SQL:
To get a date value from its parts, use the following example:
In this example, the function returns the date value 2021-09-01 that corresponds to the year 2021, month 9, and day 1.
Arguments of datetimefromparts SQL
Year Argument
The year argument is used to specify the year value of the resulting datetime data type. It must be an integer value greater than or equal to 1.
An example of using the year argument in datetimefromparts SQL is:
Month Argument
The month argument is used to specify the month value of the resulting datetime data type. It must be an integer value greater than or equal to 1 and less than or equal to 12.
An example of using the month argument in datetimefromparts SQL is:
Day Argument
The day argument is used to specify the day value of the resulting datetime data type. It must be an integer value greater than or equal to 1 and less than or equal to 31 depending on the month and year values.
An example of using the day argument in datetimefromparts SQL is:
Hour Argument
The hour argument is used to specify the hour value of the resulting datetime data type. It must be an integer value between 0 and 23.
An example of using the hour argument in datetimefromparts SQL is:
Minute Argument
The minute argument is used to specify the minute value of the resulting datetime data type. It must be an integer value between 0 and 59.
An example of using the minute argument in datetimefromparts SQL is:
Seconds and Milliseconds Arguments
The seconds argument is used to specify the second value of the resulting datetime data type. It must be an integer value between 0 and 59.
The milliseconds argument is used to specify the fractional second value of the resulting datetime data type. It must be an integer value between 0 and 999.
An example of using the seconds and milliseconds arguments in datetimefromparts SQL is:
Invalid Values and Null Values
DATETIMEFROMPARTS SQL function requires valid and correct argument values for year, month, day, hour, minute, seconds, fractions and precision. Otherwise, an error is returned. For instance, if the year is negative, DATETIMEFROMPARTS will throw an error. Moreover, if any of the first 7 arguments are NULL, the function result will also be NULL.
Conclusion
The DATETIMEFROMPARTS() function in SQL is a very useful tool in creating a date value from individual parts such as year, month, day, hour, minute, seconds, and fractions of a second with precision. This function ensures that the arguments passed are valid and returns null when any of the first 7 arguments is null. By utilizing this function, SQL programming becomes more efficient and effective in managing and manipulating date and time values.
References
Microsoft documentation on datetimefromparts SQL explains the syntax and parameters of the DATEFROMPARTS() function, which returns a date value based on the year, month, and day specified.
SQL Shack article on datetimefromparts SQL usage provides examples of how to use the DATETIME2FROMPARTS() function using various date and time formats. It also explains how to handle null values and invalid arguments in the function.
