Casting Numeric Values to DATETIME in SQL Server Transact-SQL

Q

Can I cast numeric values to DATETIME values in SQL Server Transact-SQL?

✍: FYIcenter.com

A

Yes, you can cast numeric values to DATATIME values in Transact-SQL. The implicit casting rules are for numeric values to DATETIME values are:

  • Numeric values are casted linearly to DATETIME values.
  • Decimal 0 represents 1900-01-01 00:00:00.000.
  • Decimal unit of 1 represents 1 day (or 24 hours).
  • Decimal unit of 0.1 represents 0.1 day (or 2 hours and 24 minutes).

Here are some examples of casting numeric values to DATETIME values implicitly:

DECLARE @x DATETIME;
SET @x = 0;
SELECT @x;
-----------------------
1900-01-01 00:00:00.000

DECLARE @x DATETIME;
SET @x = 1;
SELECT @x;
-----------------------
1900-01-02 00:00:00.000

DECLARE @x DATETIME;
SET @x = 0.1;
SELECT @x
-----------------------
1900-01-01 02:24:00.000

DECLARE @x DATETIME;
SET @x = -1;
SELECT @x;
-----------------------
1899-12-31 00:00:00.000

 

Variables and Data Types in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-04-15, 170👍, 0💬