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, 30481🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on Managing Security, Login and User in SQL Serv...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
How To Create a Stored Program Unit in Oracle? If you want to create a stored program unit, you can ...
How to put statements into a statement block in SQL Server Transact-SQL? You can put statements into...
How To Start MySQL Server in MySQL? If you want to start the MySQL server, you can run the "mysqld" ...