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, 28957🔥, 0💬
Popular Posts:
How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops? You can u...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
How To List All Login Names on the Server in SQL Server? If you want to see a list of all login name...
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...