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, 8833👍, 0💬
Popular Posts:
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
How To See Which Storage Engines Are Supported in Your MySQL Server in MySQL? If you want to know ex...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
How To Generate CREATE VIEW Script on an Existing View in SQL Server? If you want to know how an exi...