Menu
How can I convert a day [1-31] and a month [1-12] and a year (all int), to a date serial IN SQL (without converting to varchar)?
824
The DATEFROMPARTS() function returns a date from the specified parts (year, month, and day values).
SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS.
To declare a date variable, use the DECLARE keyword, then type the @variable_name and variable type: date, datetime, datetime2, time, smalldatetime, datetimeoffset. In the declarative part, you can set a default value for a variable. The most commonly used default value for a date variable is the function Getdate().
Zero is 01 jan 1900 in SQL, so you can use this:
DATEADD(day, @dayval-1,
DATEADD(month, @monthval-1,
DATEADD(year, @yearval-1900, 0)
)
)
Edit, Feb 2018
As the other answer says, since SQL Server 2012 (released after the original answer) we can use DATEFROMPARTS
SELECT DATEFROMPARTS (@yearval, @monthval, @dayval)
A few functions have been added in SQL Server 2012 which will allow you to do this with DATEFROMPARTS, DATETIMEFROMPARTS and TIMEFROMPARTS amongst others.
Example:
SELECT DATEFROMPARTS(2018,2,1) -- returns 2018-02-01
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
