Update only time from my Datetime field in sql
DECLARE @Time as TIME;
DECLARE @Date as DATETIME;
SELECT @Time = CONVERT(TIME, '2016-01-01 09:30:00.000');
SELECT @Date = CONVERT(date, GETDATE()); --2017-03-10 16:37:34.403
SELECT DATEADD(MINUTE, DATEPART(MINUTE, @Time), DATEADD(HH, DATEPART(HOUR, @Time), @Date))
OUTPUT: 2017-03-10 09:30:00.000
UPDATE MyTable
SET MyDate = DATEADD(HOUR, 4, CAST(CAST(MyDate AS DATE) AS DATETIME))
Or this
UPDATE MyTable
SET MyDate = DATEADD(HOUR, 4, CAST(FLOOR(CAST(MyDate AS FLOAT)) AS DATETIME))
UPDATE TABLE_NAME SET DATETIME_FIELD = CAST(CAST(CONVERT(DATE, DATETIME_FIELD,101) AS VARCHAR) + ' 2' + ':' + '22' AS DATETIME) WHERE (OUR_CONDTTION)
User the below one to update the Time only for datetime
column -
select CONVERT(varchar(10),datecolumn, 120) + ' 12:34:56',
CONVERT(varchar(10),datecolumn, 120) + ' 00:00:00',
RSLV_LTR_INITIAL_DUE_DT, *
from twhere able1