仅更新 sql 中我的日期时间字段中的时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15700355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update only time from my Datetime field in sql
提问by omkar patade
I have a date 2013-12-14 05:00:00.000 in my table.
我的表中有一个日期 2013-12-14 05:00:00.000。
Now i want to update only time of that date. E.G to 2013-12-14 04:00:00.000
现在我只想更新那个日期的时间。EG 至 2013-12-14 04:00:00.000
Is there any query to update only time from datetime field?
是否有任何查询仅更新日期时间字段中的时间？
回答by Yuriy Rozhovetskiy
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))
回答by Sumanth
User the below one to update the Time only for datetimecolumn -
用户使用下面的一个来更新 Time only fordatetime列 -
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
回答by Samina
UPDATE TABLE_NAME SET DATETIME_FIELD = CAST(CAST(CONVERT(DATE, DATETIME_FIELD,101) AS VARCHAR) + ' 2' + ':' + '22' AS DATETIME) WHERE (OUR_CONDTTION)
回答by Pranesh Janarthanan
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
输出：2017-03-10 09:30:00.000
回答by Nabster
In case of just updating a particular part of the datetime you can use SMALLDATETIMEFROMPARTSlike:
如果只是更新日期时间的特定部分，您可以使用SMALLDATETIMEFROMPARTS：
UPDATE MyTable
SET MyDate = SMALLDATETIMEFROMPARTS(YEAR(MyDate), MONTH(MyDate), DAY(MyDate),YEAR(MyDate), <HoursValue>, <MinutesValue>)
In other cases it may be required to copy parts of datetime to other or update only certain parts of the datetime:
在其他情况下，可能需要将部分日期时间复制到其他部分或仅更新日期时间的某些部分：
UPDATE MyTable
SET MyDate = SMALLDATETIMEFROMPARTS(YEAR(MyDate), MONTH(MyDate), DAY(MyDate),YEAR(MyDate), DATEPART(hour, MyDate), DATEPART(minute, MyDate))
Refer SQL Server Date/Time related API referencesfor more such functions
有关更多此类功能，请参阅SQL Server 日期/时间相关 API 参考
回答by Abolfazl
use this script:
使用这个脚本：
declare @curDate datetime=getdate() --or any other datetime
declare @time time='22:31'
declare @hour int ,@min int
set @hour=datepart(hh,@time)
set @min=datepart(mm,@time)
select @curDate=Dateadd(HOUR,@hour,convert(datetime,convert(Date,@curDate)))
select @curDate=Dateadd(MINUTE,@min,@curDate)
Select @curDate
回答by Amir Alizade
DECLARE @d datetime;
SELECT @d = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
DECLARE @t time(7);
SET @t = CAST('10:10:10' AS time);
SELECT CONVERT(datetime, CONVERT(varchar(10), CONVERT(date, @d, 101)) + ' ' + CONVERT(varchar(8), @t));
回答by Jahangir Alam
The previous query update only the hour portion but this query will update all portions of the time i.e hour, minutes, seconds:
上一个查询只更新小时部分，但此查询将更新时间的所有部分，即小时、分钟、秒：
UPDATE TABLE_NAME
SET DATETIME_FIELD = CONCAT(CAST(DATETIME_FIELD AS Date), ' ', CAST('2016-02-01 09:20:00.0000000' AS TIME))
