Are DATETIME and NUMERIC Values Convertible in SQL Server Transact-SQL?

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

 

⇒Date/Time Operations and Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials