Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL?

Can date and time values be converted into integers? The answer is yes. The resulting integer will be the number days relative the base date: Jan 1, 1900. The time of the day will be rounded to days. But there several rules you need to follow:

• Date and time values can not be converted to integers implicitly using assignment operations.
• Date and time values can be converted to integers explicitly using CAST() or CONVERT() functions.

Here are some good examples:

-- Implicit conversion is not allowed
DECLARE @birth_date DATETIME;
DECLARE @days INT;
SET @birth_date = '29-Feb-2000 03:00:00.000';
SET @days = @birth_date;
GO
Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type datetime to int is
   not allowed. Use the CONVERT function to run this query.

-- Explicit conversion is supported
DECLARE @birth_date DATETIME;
SET @birth_date = '29-Feb-2000 03:00:00.000';
SELECT CONVERT(INT, @birth_date);
GO
36583

-- 36583 days since the base date?
DECLARE @birth_date DATETIME;
SET @birth_date = '01-Jan-1900 00:00:00.000';
SELECT @birth_date + 36583;
GO
2000-02-29 00:00:00.000


-- Time of the day will be rounded to days
DECLARE @birth_date DATETIME;
SET @birth_date = '29-Feb-2000 13:00:00.000';
SELECT CONVERT(INT, @birth_date);
GO
36584

 

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

⇒⇒SQL Server Transact-SQL Tutorials