Converting Date and Time Values into Integers in SQL Server

Q

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

✍: FYIcenter.com

A

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

2017-02-22, 350👍, 0💬