Truncating DATETIME Values to Dates without Time in SQL Server

Q

How To Truncate DATETIME Values to Dates without Time in SQL Server Transact-SQL?

✍: FYIcenter.com

A

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

⇒ Setting New Values to Parts of a DATETIME Value in SQL Server

⇐ CONVERT() - Formatting DATETIME Values to Strings in SQL Server

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

⇑⇑ SQL Server Transact-SQL Tutorials

2017-02-08, 2573🔥, 0💬

Formatting Time Zone in +/-hh:mm Format in SQL Server
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, you know how to calculate the time zone value by using GETDATE() and GETUTCDATE() functions. But how can you format that time zone value in a nice looking format like +05:45 or -04:00? Unfortunately, S... 2017-02-08, 4720🔥, 0💬

CONVERT() - Formatting DATETIME Values to Strings in SQL Server
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL Server 2005 offers no functions to format DATETIME values in your own format patterns. But it does provide you a number of pre-defined format patterns that you can use with the CONVERT(char_type, dat... 2017-02-08, 4632🔥, 0💬

Getting Year, Month and Day Out of DATETIME Values in SQL Server
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPART() to get any part of the DATETIME value. But to get year, month and day, you can use 3 specific functions: YEAR(), MONTH() and DAY(). These functions are equivalent to DATEPART() as: YEAR(date) = D... 2017-02-14, 4375🔥, 0💬

Setting New Values to Parts of a DATETIME Value in SQL Server
How To Set Different Parts of a DATETIME Value in SQL Server Transact-SQL? In SQL Server, you can get different parts of a DATETIME value with the DATEPART() functions. But there is no function that allows you to set different parts to a DATETIME value. For example, you a date_of_birth column as DAT... 2017-02-08, 2855🔥, 0💬

Truncating DATETIME Values to Dates without Time in SQL Server
How To Truncate DATETIME Values to Dates without Time in SQL Server Transact-SQL? 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 solut... 2017-02-08, 2574🔥, 0💬

What Are NULL Values in SQL Server
What Are NULL Values in SQL Server Transact-SQL? A NULL value is a special value that represents an unknown value. SQL Server supports NULL values with the following features: All data types used for table columns support NULL values. In another word, NULL values can be stored in database tables. In... 2017-02-05, 2383🔥, 0💬

NULL Values Involved in Arithmetic Operations in SQL Server
What Happens If NULL Values Are Involved in Arithmetic Operations in SQL Server Transact-SQL? If NULL values are involved in arithmetic operations, the result will be numeric NULL values. The following tutorial script shows you some good examples: SELECT 7+NULL; GO ----------- NULL SELECT 10.02*NULL... 2017-02-05, 2232🔥, 0💬

Working with NULL Values in SQL Server Transact-SQL
Where to find answers to frequently asked questions on Working with NULL Values in SQL Server Transact-SQL? Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on Working with NULL Values in SQL Server Transact-SQL. Clear explanations and tutorial exerci... 2017-02-08, 2216🔥, 0💬

Getting Parts of DATETIME Values as Integers in SQL Server
How To Get Parts of DATETIME Values as Integers in SQL Server Transact-SQL? Sometimes, you want to one specific part of a DATETIME value as an integer to be used in your own date and time calculations. This can be done by using the DATEPART() function in the following format: DATENAME(datepart, date... 2017-02-14, 2146🔥, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.