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
⇒ Working with NULL Values in SQL Server Transact-SQL
⇐ Truncating DATETIME Values to Dates without Time in SQL Server
⇑ Date/Time Operations and Functions in SQL Server Transact-SQL
2017-02-08, 2523🔥, 0💬
Popular Posts:
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
What Is SQL*Plus in Oracle? SQL*Plus is an interactive and batch query tool that is installed with e...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...
What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...
How To Verify a User name with SQLCMD Tool in SQL Server? The quickest way to verify a user name in ...