Create a date from day month and year with T-SQL

by

Try this:

Declare @DayOfMonth TinyInt Set @DayOfMonth = 13
Declare @Month TinyInt Set @Month = 6
Declare @Year Integer Set @Year = 2006
-- ------------------------------------
Select DateAdd(day, @DayOfMonth - 1, 
          DateAdd(month, @Month - 1, 
              DateAdd(Year, @Year-1900, 0)))

It works as well, has added benefit of not doing any string conversions, so it’s pure arithmetic processing (very fast) and it’s not dependent on any date format
This capitalizes on the fact that SQL Server’s internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900, and the second part is a decimal fraction representing the fractional portion of one day (for the time) — So the integer value 0 (zero) always translates directly into Midnight morning of 1 Jan 1900…

or, thanks to suggestion from @brinary,

Select DateAdd(yy, @Year-1900,  
       DateAdd(m,  @Month - 1, @DayOfMonth - 1))

Edited October 2014. As Noted by @cade Roux, SQL 2012 now has a built-in function:
DATEFROMPARTS(year, month, day)
that does the same thing.

Edited 3 Oct 2016, (Thanks to @bambams for noticing this, and @brinary for fixing it), The last solution, proposed by @brinary. does not appear to work for leap years unless years addition is performed first 

select dateadd(month, @Month - 1, 
     dateadd(year, @Year-1900, @DayOfMonth - 1));

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. Referring to a Column Alias in a WHERE Clause
  7. SQL query to select dates between two dates
  8. Convert Month Number to Month Name Function in SQL
  9. Selecting COUNT(*) with DISTINCT
  10. How to return multiple values in one column (T-SQL)?
  11. Generate a resultset of incrementing dates in TSQL
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. SQL – Subtracting a depleting value from rows
  16. How to print VARCHAR(MAX) using Print Statement?
  17. What’s the best way to select the minimum value from several columns?
  18. Concatenate values based on ID
  19. SELECT FOR UPDATE with SQL Server
  20. varbinary to string on SQL Server
  21. SQL Server 2005 ROW_NUMBER() without ORDER BY
  22. How to split string using delimiter char using T-SQL?
  23. Can an INNER JOIN offer better performance than EXISTS
  24. OPENXML with xmlns:dt
  25. SQL server select distinct rows using most recent value only
  26. Eliminate and reduce overlapping date ranges
  27. Cumulative Total in MS Sql server [duplicate]
  28. Why do SQL Server Scalar-valued functions get slower?
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment