Collections:
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?
✍: FYIcenter.com
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 DATETIME in a table, you want to set all rows to the noon time in this column.
The tutorial exercise below shows how to set year, month, day, hour, minute and second to any give DATETIME value:
-- set parts to a DATETIME value DECLARE @date_of_birth DATETIME; SET @date_of_birth = GETDATE(); SET @date_of_birth = DATEADD(YEAR, 1987-DATEPART(YEAR,@date_of_birth), @date_of_birth); SET @date_of_birth = DATEADD(MONTH, 05-DATEPART(MONTH,@date_of_birth), @date_of_birth); SET @date_of_birth = DATEADD(DAY, 19-DATEPART(DAY,@date_of_birth), @date_of_birth); SET @date_of_birth = DATEADD(HOUR, 12-DATEPART(HOUR,@date_of_birth), @date_of_birth); SET @date_of_birth = DATEADD(MINUTE, 00-DATEPART(MINUTE,@date_of_birth), @date_of_birth); SET @date_of_birth = DATEADD(SECOND, 00-DATEPART(SECOND,@date_of_birth), @date_of_birth); SELECT 'You were born on ' + CONVERT(VARCHAR(40),@date_of_birth,107) + ', at ' + CONVERT(VARCHAR(40),@date_of_birth,108); GO You were born on May 19, 1987, at 12:00:00
⇒Date/Time Operations and Functions in SQL Server Transact-SQL
2017-02-08, 797👍, 0💬
Popular Posts:
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
How to download and install SQL Server 2005 Sample Scripts in SQL Server? If you want to learn from ...