Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Truncating DATETIME Values to Dates without Time

By: FYIcenter.com

(Continued from previous topic...)

How To Truncate DATETIME Values to Dates without Time?

Assuming that you have some date and time, DATETIME, values, and you want to work with dates only. You want to truncate them to dates without time, or with time of 00:00:00.000.

Again, SQL Server offers no simple solution, you need to write the formula yourself. The tutorial exercise below shows you how to do this with CONVERT() and FLOOR() functions:

-- truncating to dates
DECLARE @local_time DATETIME;
SET @local_time = 'May 19 2007 10:06PM';
SELECT 'Server local date and time: ';
SELECT @local_time;
SELECT 'Server local date without time: ';
SELECT CONVERT(DATETIME,
   FLOOR(CONVERT(NUMERIC(18,9),@local_time)));
GO
Server local date and time: 
2007-05-19 22:06:00.000
Server local date without time: 
2007-05-19 00:00:00.000

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