Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Converting DATETIME and NUMERIC Values

By: FYIcenter.com

(Continued from previous topic...)

Are DATETIME and NUMERIC Values Convertible?

Are datetime and numeric value convertible? The answer is yes. Here are the main rules on DATATIME and NUMERIC value conversions:

  • During the conversion a DATETIME value will be treated as a NUMERIC value with the number of days relative the base date, Jan 1, 1900 being the integer part, and the time of the day being the decimal part.
  • DATETIME values can not be converted to NUMERIC values implicitly using assignment operations.
  • NUMERIC values can be converted to DATETIME values implicitly using assignment operations.
  • DATETIME values and DATETIME values can be converted to each other explicitly using CAST() or CONVERT() functions.

The tutorial exercise below shows you some good examples:

-- Implicit conversion NUMERIC to DATETIME
DECLARE @birth_date DATETIME;
SET @birth_date = 36583.25;
SELECT @birth_date;
GO
2000-02-29 06:00:00.000

-- Explicit conversion NUMERIC to DATETIME
DECLARE @birth_date DATETIME;
SET @birth_date = CONVERT(DATETIME, 36583.75);
SELECT @birth_date;
GO
2000-02-29 18:00:00.000

-- Implicit conversion DATETIME to NUMERIC
DECLARE @birth_date DATETIME;
DECLARE @birth_days NUMERIC(9,2);
SET @birth_date = '2000-02-29 06:00:00.000';
SET @birth_days = @birth_date;
GO
Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type datetime to numeric is 
   not allowed. Use the CONVERT function to run this query.

-- Explicit conversion DATETIME to NUMERIC
DECLARE @birth_date DATETIME;
DECLARE @birth_days NUMERIC(9,2);
SET @birth_date = '2000-02-29 18:00:00.000';
SET @birth_days = CONVERT(NUMERIC(9,2), @birth_date);
SELECT @birth_days;
GO
36583.75

(Continued on next topic...)

  1. How To Add or Remove Days on Date and Time Values?
  2. Can Date and Time Values Be Converted into Integers?
  3. Can Integers Be Converted into Date and Time Values?
  4. Are DATETIME and NUMERIC Values Convertible?
  5. Can a DATETIME Value Be Subtracted from Another DATETIME Value?
  6. What Are the Date and Time Functions Supported by SQL Server 2005?
  7. How To Increment or Decrement Parts of DATETIME Values?
  8. How To Use DATEADD() Function?
  9. How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?
  10. How To Calculate Age in Days, Hours and Minutes?
  11. How To Get Month and Weekday Names from DATETIME Values?
  12. How To Get Parts of DATETIME Values as Integers?
  13. How To Get Year, Month and Day Out of DATETIME Values?
  14. What Is the Difference Between GETDATE() and GETUTCDATE()?
  15. How To Format Time Zone in +/-hh:mm Format?
  16. How To Format DATETIME Values to Strings with the CONVERT() Function?
  17. How To Truncate DATETIME Values to Dates without Time?
  18. How To Set Different Parts of a DATETIME Value?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...