SQL 设置日期时间变量的时间部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4952207/
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 portion of a datetime variable
提问by NA Slacker
I am working on a query that will be an automated job. It needs to find all the transactions between 8 PM and 8 PM for the last day. I was thinking of doing something like this
我正在处理一个将成为自动化工作的查询。它需要查找最后一天晚上 8 点到晚上 8 点之间的所有交易。我正在考虑做这样的事情
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET @start_date = DATEADD(DAY, -2, GETDATE())
SET @end_date = DATEADD(DAY, -1, GETDATE())
For an automated query this works good at figuring out the date portion. But the TIME portion of the variable is the current time that the query executes. Is there a quick simple way to hard code the time portion of both variables to be 8:00 PM?
对于自动查询，这可以很好地找出日期部分。但是变量的 TIME 部分是查询执行的当前时间。是否有一种快速简单的方法可以将两个变量的时间部分硬编码为晚上 8:00？
回答by RichardTheKiwi
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET @start_date = DATEADD(hour, 20, DATEDIFF(DAY, 2, GETDATE()))
SET @end_date = @start_date + 1
select @start_date, @end_date
回答by Will Marcouiller
This will also work:
这也将起作用：
DECLARE @start_date datetime
DECLARE @end_date datetime
SET @start_date = LEFT(CONVERT(nvarchar, DATEADD(DAY, -2, GETDATE()), 120), 11) + N'20:00:00'
SET @end_date = @start_date + 1
select @start_date, @end_date
Although cyberkiwi's answer is very clever! =)
虽然cyberkiwi的回答很聪明！=)
回答by Max Pringle
I needed to pull a date from the database and append 3:00 Pm to it. I did it this way
我需要从数据库中提取一个日期并将 3:00 Pm 附加到它。我是这样做的
select dateadd(hour, 15, datediff(day, 0, myDatabaseDate))
from dbo.myDatabaseTable
where myDatabaseId = 1
The result that it returned was 2017-10-01 15:00:00.000. The date in the database is 2017-10-01. The solution that I proposed was to keep my current date. I added 0 days to my existing date. I gave it 15:00 hours and it worked like a charm.
它返回的结果是2017-10-01 15:00:00.000。数据库中的日期是2017-10-01. 我提出的解决方案是保留当前日期。我在现有日期上增加了 0 天。我给了它 15:00 小时，它就像一个魅力。
回答by WonderWorker
DECLARE @start_date DATETIME = DATEADD(HOUR, 20, DATEADD(MINUTE, 00, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))) - 2
DECLARE @end_date DATETIME = DATEADD(HOUR, 20, DATEADD(MINUTE, 00, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))) - 1
Notes:
笔记：
GETDATE() + Xis the equivalent ofDATEADD(DAY, X, GETDATE()).- Converting a
DATEIMEto aDATEand then back to aDATETIMEagain sets the time to midnight i.e.00:00:00.000. - Seperate
SETandDECLAREstatements are unnecessary, but just in case it helps later, variables may be set as part of aSELECTstatement too.
GETDATE() + X相当于DATEADD(DAY, X, GETDATE())。- 将 a 转换
DATEIME为 aDATE然后再转换回 aDATETIME将时间设置为午夜 ie00:00:00.000。 - 单独的
SET和DECLARE语句是不必要的，但以防万一它以后有帮助，变量也可以设置为SELECT语句的一部分。
回答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 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.
希望这可以帮助某人。
