Collections:
Converting Date and Time Values into Integers in SQL Server
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL?
✍: FYIcenter.com
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:
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
2017-02-22, 838👍, 0💬
Popular Posts:
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
Where to find answers to frequently asked questions on Using INSERT, UPDATE and DELETE Statements in...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...
How To Concatenate Two Text Values in Oracle? There are two ways to concatenate two text values toge...
How To Get the Definition of a User Defined Function Back in SQL Server Transact-SQL? If you want ge...