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

Constructing DATETIME Values in T-SQL from Individual Date Parts

In older versions of SQL Server (like SQL Server 2005), creating a DATETIME value directly from separate day, month, and year components can be tricky and prone to errors. This guide outlines several methods to achieve this reliably.

Method 1: Using DATEADD for Datetime Construction

This method leverages the DATEADD function to build the DATETIME step-by-step:

<code class="language-sql">DECLARE @DayOfMonth TINYINT; SET @DayOfMonth = 13;
DECLARE @Month TINYINT; SET @Month = 6;
DECLARE @Year INT; SET @Year = 2006;

SELECT DATEADD(day, @DayOfMonth - 1, 
              DATEADD(month, @Month - 1, 
                      DATEADD(year, @Year - 1900, 0)));</code>

The subtraction of 1 from @DayOfMonth and @Month accounts for the fact that DATEADD works with offsets from the base date (January 1st, 1900).

Method 2: Alternative DATEADD Syntax

A more concise approach using DATEADD is:

<code class="language-sql">SELECT DATEADD(yy, @Year - 1900,  
               DATEADD(m, @Month - 1, @DayOfMonth -1));</code>

This achieves the same result with a slightly different arrangement of the DATEADD calls.

Method 3: DATEFROMPARTS (SQL Server 2012 and later)

SQL Server 2012 and later versions offer the convenient DATEFROMPARTS function:

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

This is the most straightforward method if your SQL Server version supports it.

Handling Leap Years

While DATEFROMPARTS automatically handles leap years, the DATEADD methods are robust enough to handle them correctly as well. No special adjustments are needed for leap years in the provided examples.

Choose the method most suitable for your SQL Server version and coding style. DATEFROMPARTS is the preferred approach for its simplicity and clarity if your SQL Server version allows it.

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