General MySQL Oracle SQL Server

Converting Integers into Date and Time Values in SQL Server

Q

Can Integers Be Converted into Date and Time Values in SQL Server Transact-SQL?

✍: FYIcenter.com

A

Can integers be converted into date and time values? The answer is yes. The input integer will be used as the number of days relative to the base date: Jan 1, 1900. The time of the day will be set to 0, midnight of the day. But there several rules you need to follow:

  • Integers can be converted to date and time values implicitly using assignment operations.
  • Integers can be converted to date and time values explicitly using CAST() or CONVERT() functions.

Here are some good examples: 

-- Explicit conversion is supported
DECLARE @birth_date DATETIME;
SET @birth_date = CONVERT(DATETIME, 36583);
SELECT @birth_date;
GO
2000-02-29 00:00:00.000

-- Explicit conversion is supported
DECLARE @birth_date DATETIME;
SET @birth_date = CONVERT(DATETIME, 36583);
SELECT @birth_date;
GO
2000-02-29 00:00:00.000

 

⇒ Converting DATETIME and NUMERIC Values in SQL Server

⇐ Converting Date and Time Values into Integers in SQL Server

⇑ Date/Time Operations and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2017-02-22, 2180🔥, 0💬

General MySQL Oracle SQL Server