SQL Server FAQ - Converting DATETIME and NUMERIC Values By: FYIcenter.com (Continued from previous topic...) Are DATETIME and NUMERIC Values Convertible? Are datetime and numeric value convertible? The answer is yes. Here are the main rules on DATATIME and NUMERIC value conversions: During the conversion a DATETIME value will be treated as a NUMERIC value with the number of days relative the base date, Jan 1, 1900 being the integer part, and the time of the day being the decimal part. DATETIME values can not be converted to NUMERIC values implicitly using assignment operations. NUMERIC values can be converted to DATETIME values implicitly using assignment operations. DATETIME values and DATETIME values can be converted to each other explicitly using CAST() or CONVERT() functions. The tutorial exercise below shows you some good examples: ```-- Implicit conversion NUMERIC to DATETIME DECLARE @birth_date DATETIME; SET @birth_date = 36583.25; SELECT @birth_date; GO 2000-02-29 06:00:00.000 -- Explicit conversion NUMERIC to DATETIME DECLARE @birth_date DATETIME; SET @birth_date = CONVERT(DATETIME, 36583.75); SELECT @birth_date; GO 2000-02-29 18:00:00.000 -- Implicit conversion DATETIME to NUMERIC DECLARE @birth_date DATETIME; DECLARE @birth_days NUMERIC(9,2); SET @birth_date = '2000-02-29 06:00:00.000'; SET @birth_days = @birth_date; GO Msg 257, Level 16, State 3, Line 4 Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query. -- Explicit conversion DATETIME to NUMERIC DECLARE @birth_date DATETIME; DECLARE @birth_days NUMERIC(9,2); SET @birth_date = '2000-02-29 18:00:00.000'; SET @birth_days = CONVERT(NUMERIC(9,2), @birth_date); SELECT @birth_days; GO 36583.75 ``` (Continued on next topic...)
 Related Articles: More... Other Tutorials/FAQs: More... Related Resources: More... Selected Jobs: More...