SQL 将 datetime 变量的时间部分设置为 18:00
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2847851/
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
Set time part of datetime variable to 18:00
提问by Max Al Farakh
I need to set datetime variable to two days from now but it's time part must be 18:00.
我需要将日期时间变量设置为两天后，但它的时间部分必须是 18:00。
For example if i call getdate()now i'll get 2010-05-17 13:18:07.260. I need to set it to 2010-05-19 18:00:00.000.
例如，如果我getdate()现在打电话，我会得到2010-05-17 13:18:07.260. 我需要将其设置为2010-05-19 18:00:00.000.
Does anybody have a good snippet for that or any ideas how to do it right?
有没有人有一个很好的片段或任何想法如何正确地做到这一点？
回答by Alex
SELECT DATEADD(hh, 24 * 2 + 18, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
This truncates the current date and adds 2 days and 18 hours to it (24 * 2 + 18).
这将截断当前日期并为其添加 2 天 18 小时(24 * 2 + 18)。
A possible variation:
一个可能的变化：
SELECT DATEADD(hh, 18, DATEADD(dd, DATEDIFF(dd, -2, GETDATE()), 0))
回答by Najam
I had to do something similar, create a procedure to run from a certain time the previous day to a certain time on the current day This is what I did to set the start date to 16:30 on the previous day, basically subtract the parts you don't want to get them back to 0 then add the value that you want it to be.
我不得不做类似的事情，创建一个程序从前一天的某个时间运行到当天的某个时间这就是我将开始日期设置为前一天的 16:30 所做的，基本上减去部分你不想让它们回到 0 然后添加你想要的值。
-- Set Start Date to previous day and set start time to 16:30.00.000
SET @StartDate = GetDate()
SET @StartDate = DateAdd(dd,- 1, @StartDate)
SET @StartDate = DateAdd(hh,- (DatePart(hh,@StartDate))+16, @StartDate)
SET @StartDate = DateAdd(mi,- (DatePart(mi,@StartDate))+30, @StartDate)
SET @StartDate = DateAdd(ss,- (DatePart(ss,@StartDate)), @StartDate)
SET @StartDate = DateAdd(ms,- (DatePart(ms,@StartDate)), @StartDate)
Hope this helps someone.
希望这可以帮助某人。
回答by WonderWorker
In short, the answer is...
简而言之，答案是……
DECLARE @MyDate DATETIME = DATEADD(HOUR, 18, CONVERT(DATETIME, CONVERT(DATE, GETDATE()+2))) [6pm Today]
It probably looks pretty complicated, but believe me when I say it looks a lot worse than it really is.
它可能看起来很复杂，但相信我，当我说它看起来比实际情况要糟糕得多时。
To explain each function call as a sequence of steps for the query above is as follows:
将每个函数调用解释为上述查询的一系列步骤，如下所示：
GETDATE(): Gets the current date and time as aDATETIMEvalue.+2: Add two days, since each whole number in DATETIME calulations represents a day, with fractions representing fractions of a day.CONVERT(DATE,@REF): Convert to date format (to remove the time element)CONVERT(DATETIME,@REF): Convert back to DateTime (Time element defaults to midnight 00:00)DATEADD(HOUR,@NUM,@REF): Add 12 + 6 = 18 hours to get 6 in the afternoon
GETDATE(): 获取当前日期和时间作为DATETIME值。+2：添加两天，因为 DATETIME 计算中的每个整数代表一天，分数代表一天的分数。CONVERT(DATE,@REF): 转换为日期格式（去除时间元素）CONVERT(DATETIME,@REF)：转换回DateTime（时间元素默认为午夜00:00）DATEADD(HOUR,@NUM,@REF): 下午加 12 + 6 = 18 小时得到 6
Notes:
笔记：
Change
@MyDateto reflect the name of your own variable, bearing in mind that variables have a@prefix.Years may be added by wrapping the query between the
=and the[6pm Today]in aDATEADD(YEAR,@NUM,@REF)function.- Months may be added by wrapping the query between the
=and the[6pm Today]in aDATEADD(MONTH,@NUM,@REF)function. - Minutes may be added by wrapping the query between the
=and the[6pm Today]in aDATEADD(MINUTE,@NUM,@REF)function. - Seconds may be added by wrapping the query between the
=and the[6pm Today]in aDATEADD(SECOND,@NUM,@REF)function. Milliseconds may be added by wrapping the query between the
=and the[6pm Today]in aDATEADD(MILLISECOND,@NUM,@REF)function.I personally avoid
DATEDIFFfunction, because it returns a 32bit int, which overflows often since theDateTimevalue type is stored internally as a 64bit value and represents dates and times with values ranging from 00:00:00 (midnight), January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) in the Gregorian calendar.I use
CONVERTinstead ofCASTsinceCASTcalls theCONVERTfunction. It probably makes barely any difference to execution time, but if it does then it makes sense to callCONVERTdirectly.The white-space in the queries is mostly optional.
更改
@MyDate以反映您自己的变量的名称，记住变量有一个@前缀。岁月可以通过包装之间的查询中添加
=和[6pm Today]一个DATEADD(YEAR,@NUM,@REF)功能。- 个可以通过包装之间的查询被添加
=并且[6pm Today]在一个DATEADD(MONTH,@NUM,@REF)函数。 - 分钟可能通过包装之间的查询中添加
=和[6pm Today]一个DATEADD(MINUTE,@NUM,@REF)功能。 - 秒可以由缠绕之间的查询被添加
=并且[6pm Today]在一个DATEADD(SECOND,@NUM,@REF)函数。 毫秒可以通过包装之间的查询被添加
=并且[6pm Today]在一个DATEADD(MILLISECOND,@NUM,@REF)函数。我个人避免使用
DATEDIFF函数，因为它返回一个 32 位整数，它经常溢出，因为DateTime值类型在内部存储为 64 位值，并表示日期和时间的值范围为 00:00:00（午夜），0001 年 1 月 1 日 Anno Domini (Common Era) 到公历 9999 年 12 月 31 日 (CE) 晚上 11:59:59。我使用
CONVERT而不是CAST因为CAST调用CONVERT函数。它可能对执行时间几乎没有任何影响，但如果确实如此，那么CONVERT直接调用是有意义的。查询中的空格大多是可选的。
回答by codingbadger
Select DateAdd(hour, 18, DateAdd(day, 2, cast(floor(cast(getdate() as float))as datetime)))
回答by Oleg
A bit late but still a useful example:
有点晚了，但仍然是一个有用的例子：
declare
@dt datetime = getutcdate()
,@hour tinyint = 18;
select
@dt as dt
,DATEPART(hour, @dt) as hour_of_day
,DATEADD(hour, -1*DATEPART(hour, @dt), @dt) as hour_00_of_day
,DATEADD(hour, -1*DATEPART(hour, @dt),
DATEADD(minute, -1*DATEPART(minute, @dt),
DATEADD(second, -1*DATEPART(second, @dt),
DATEADD(millisecond, -1*DATEPART(millisecond, @dt), @dt)))) as beginning_of_day
,DATEADD(hour, @hour,
DATEADD(hour, -1*DATEPART(hour, @dt),
DATEADD(minute, -1*DATEPART(minute, @dt),
DATEADD(second, -1*DATEPART(second, @dt),
DATEADD(millisecond, -1*DATEPART(millisecond, @dt), @dt))))
) as hour_18_of_day
And generally as a function:
通常作为一个函数：
-- =============================================
-- Description: Set datetime part (NULL to keep as is)
-- =============================================
-- SELECT GETUTCDATE() AS DT, [dbo].[udf_datetime_SET_DATE_TIME_PART] (GETUTCDATE(), 1985, 11, 19, 12, 01, 02, 0)
-- SELECT GETUTCDATE() AS DT, [dbo].[udf_datetime_SET_DATE_TIME_PART] (GETUTCDATE(), NULL, NULL, NULL, 12, NULL, NULL, NULL)
CREATE FUNCTION [dbo].[fn_datetime_SET_DATE_TIME_PART]
(
@dt DATETIME
,@YEAR INT = NULL
,@MONTH INT = NULL
,@DAY_OF_MONTH INT = NULL
,@HOUR INT = NULL
,@MINUTE INT = NULL
,@SECOND INT = NULL
,@MILLISECOND INT = NULL
)
RETURNS DATETIME
AS
BEGIN
IF @dt IS NULL
RETURN @dt
SET @dt = IIF(@YEAR IS NULL
,@dt
,DATEADD(year, -1*DATEPART(year, @dt) + @YEAR, @dt)
)
SET @dt = IIF(@MONTH IS NULL
,@dt
,DATEADD(month, -1*DATEPART(month, @dt) + @MONTH, @dt)
)
SET @dt = IIF(@DAY_OF_MONTH IS NULL
,@dt
,DATEADD(day, -1*DATEPART(day, @dt) + @DAY_OF_MONTH, @dt)
)
SET @dt = IIF(@HOUR IS NULL
,@dt
,DATEADD(hour, -1*DATEPART(hour, @dt) + @HOUR, @dt)
)
SET @dt = IIF(@MINUTE IS NULL
,@dt
,DATEADD(minute, -1*DATEPART(minute, @dt) + @MINUTE, @dt)
)
SET @dt = IIF(@SECOND IS NULL
,@dt
,DATEADD(second, -1*DATEPART(second, @dt) + @SECOND, @dt)
)
SET @dt = IIF(@MILLISECOND IS NULL
,@dt
,DATEADD(millisecond, -1*DATEPART(millisecond, @dt) + @MILLISECOND, @dt)
)
RETURN @dt
END
