How to Correctly Create a Datetime Value from Day, Month, and Year in T-SQL?

T-SQL date and time value construction: correctly create date and time from day, month and year values ​​

Converting discrete date components into a single datetime value is a common task in SQL Server. This guide explores the correct way to create a datetime from day, month, and year values ​​to ensure accurate date representation.

The problem

Using the DATEPART function to combine individual date components often produces incorrect results. The expression CAST(DATEPART(year, DATE) '-' DATEPART(month, DATE) '-' DATEPART(day, DATE) AS DATETIME) provided in the question does not assemble the datetime value correctly.

Solution

The best way to create a datetime from a specific date component is to perform arithmetic operations on the values. The following statement demonstrates this approach:

<code class="language-sql">SELECT DateAdd(day, @DayOfMonth - 1, 
          DateAdd(month, @Month - 1, 
              DateAdd(Year, @Year-1900, 0)))</code>

This statement takes advantage of the internal representation of datetime values ​​in SQL Server, where the first part represents the number of days since January 1, 1900, and the second part represents the fractional part of the day (the time).

Alternatives

Starting with SQL Server 2012, a built-in function named DATEFROMPARTS(year, month, day) was introduced that can be used directly for this purpose:

<code class="language-sql">SELECT DATEFROMPARTS(@Year, @Month, @DayOfMonth)</code>

Leap year considerations

When dealing with leap years, always make sure to perform year addition first to account for the extra day in February. The following modified statement solves the problem:

<code class="language-sql">SELECT DateAdd(month, @Month - 1, 
     DateAdd(year, @Year-1900, @DayOfMonth - 1))</code>

Conclusion

By employing these methods, developers can efficiently create datetime values ​​from individual day, month, and year components in T-SQL, ensuring accuracy of date representation and correct handling of leap years.

The above is the detailed content of How to Correctly Create a Datetime Value from Day, Month, and Year in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!