The DATEFROMPARTS function in SQL Server allows you to create a date value using individual year, month, and day values. The function is particularly useful for inserting or manipulating date data in your SQL queries.
Syntax:
DATEFROMPARTS ( year_value, month_value, day_value )
Parameters:
- year_value: An integer representing the year.
- month_value: An integer representing the month (1 through 12).
- day_value: An integer representing the day (1 through 31, depending on the month and year).
Example:
Here is a simple example of how to use DATEFROMPARTS to create a date:
DECLARE @Year INT = 2023;
DECLARE @Month INT = 10;
DECLARE @Day INT = 5;
SELECT DATEFROMPARTS(@Year, @Month, @Day) AS CreatedDate;
In this example, the output will be 2023-10-05.
Important Notes:
1. Invalid Dates: If you pass values that do not constitute a valid date (such as February 30th or a month value less than 1 or greater than 12), SQL Server will raise an error.
2. Return Type: The DATEFROMPARTS function returns a date data type.
3. Usage in INSERT Statement: You can directly use this function in an INSERT statement to store a date in a table.
Example in INSERT Statement:
INSERT INTO YourTable (YourDateColumn)
VALUES (DATEFROMPARTS(2023, 10, 5));
This statement inserts the date "2023-10-05" into the YourDateColumn of YourTable.
In summary, DATEFROMPARTS is a straightforward and effective way to construct date values from separate integers representing year, month, and day.