Two Trusty Methods for Building Dates in T-SQL

Using CONVERT and CAST:

This method involves converting the individual day, month, and year values into a string format that represents the date and then converting it back to a date data type.

Example:

DECLARE @Year int = 2024, @Month int = 2, @Day int = 28;

-- Combine year, month, and day into a string 
DECLARE @DateStr varchar(10) = CONVERT(varchar(10), @Year * 10000 + @Month * 100 + @Day, 112);

-- Convert the string to a date
SELECT CONVERT(date, @DateStr, 112) AS ConstructedDate;

Explanation:

• We declare variables for @Year, @Month, and @Day.
• We construct a string @DateStr by combining the year, month, and day values in a specific order (YYYYMMDD).
  • We use the CONVERT function with style 112 to achieve this.
• Finally, we convert the @DateStr back to a date using CONVERT with style 112.

Using DATEFROMPARTS (Available in SQL Server 2012 and later):

This method is a dedicated function introduced in SQL Server 2012 that directly constructs a date from the provided year, month, and day values.

DECLARE @Year int = 2024, @Month int = 2, @Day int = 28;

SELECT DATEFROMPARTS(@Year, @Month, @Day) AS ConstructedDate;

• We directly use the DATEFROMPARTS function, passing the year, month, and day values as arguments.
• The function returns the constructed date.

Invalid Date Combinations:

Both methods will raise an error if the provided day, month, and year combination doesn't represent a valid date (e.g., February 31st).

Solution:

• Implement error handling mechanisms using TRY...CATCH blocks to gracefully handle invalid input and provide informative error messages.
• You can also use functions like ISDATE to check if the constructed date is valid before using it further in your code.

Time Portion:

These methods only create the date portion (without time). If you need a specific time, you can use functions like DATEADD or GETDATE to add time components to the constructed date.