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
⇒ Converting Integers into Date and Time Values in SQL Server
⇐ Adding and Removing Days on Date and Time Values in SQL Server
⇑ Date/Time Operations and Functions in SQL Server Transact-SQL
2017-02-22, 30746🔥, 0💬
Popular Posts:
How AdventureWorksLT tables are related in SQL Server? There are 12 user tables defined in Adventure...
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
What Happens If the UPDATE Subquery Returns Multiple Rows in SQL Server? If a subquery is used in a ...