Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Converting Date and Time Values into Integers

By: FYIcenter.com

(Continued from previous topic...)

Can Date and Time Values Be Converted into Integers?

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:

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

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

(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...