SQL 获取今天午夜的日期和时间并添加到其中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7700216/
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
get the date and time for today at midnight and add to it
提问by user867621
Is there a sql command to get today's date at midnight and be able to add a number of minutes to it?
是否有一个 sql 命令可以在午夜获取今天的日期并能够为其添加分钟数？
采纳答案by Charles Bretana
Yes, just use datediff and dateadd functions to strip the time from any date, then add a fractional portion of a day to that number
是的，只需使用 datediff 和 dateadd 函数从任何日期中去除时间，然后将一天的小数部分添加到该数字
Declare @aDate DateTime
Set @aDate = getDate()
Declare @Minutes Integer
Set @minutes = 600 -- 10 hours
Select DateAdd(day, DateDiff(day, 0, @aDate), 0) + @minutes / 1440.0 -- 1440 min/day
-- or You could also use the dateadd again...
Select DateAdd(minute, @minutes , DateAdd(day, DateDiff(day, 0, @aDate), 0))
Both selects return 10:00 am on the same day (more or less). This works because of, well, check out this SO answer
两个选择都在同一天（或多或少）上午 10:00 返回。这是有效的，因为，看看这个 SO 答案
EDIT: Added sample script to show how this works:
编辑：添加了示例脚本来展示它是如何工作的：
declare @dtTim datetime = getDate()
declare @today datetime = dateAdd(day, dateDiff(day, 0, @dtTim ), 0)
select @dtTim, @today
回答by Carrisi
Date now midnight time:
日期现在午夜时间：
Select DATEADD(d,0,DATEDIFF(d,0,GETDATE()))
ADD 600 Minutes:
增加 600 分钟：
Select DATEADD(mi,600,DATEDIFF(d,0,GETDATE()))
回答by Jeff Ogata
You can also cast to dateto strip off the time portion:
您还可以强制转换date为剥离时间部分：
declare @minutes int = 600
declare @start datetime = cast(getdate() as date)
declare @finish datetime = dateadd(mi, @minutes, @start)
Although, honestly, I have no idea how it performs compared to
虽然，老实说，我不知道它与
dateadd(day, datediff(day, 0, getdate()), 0)
dateadd(day, datediff(day, 0, getdate()), 0)
回答by Stefan Steiger
Necromancing:
死灵法术：
The simplest/fastest way is:
最简单/最快的方法是：
SELECT
DATEADD(minute, 1,
CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)
)
or with UTC:
或使用UTC：
SELECT
DATEADD(minute, 1,
CAST(FLOOR(CAST( GETUTCDATE() AS float)) AS datetime)
)
This works, because in SQL-server, datetime is a float value, the integer part representing the days from 01.01.1900, the sub-integer part represents the percentage of the full day (24h).
这是有效的，因为在 SQL-server 中，日期时间是一个浮点值，整数部分表示从 1900 年 1 月 1 日开始的天数，子整数部分表示全天（24 小时）的百分比。
So if you round-down (floor)the floating-point number to a integer, you get the midnight-time. 1900-01-01T00:00:00.000being 0.
因此，如果(floor)将浮点数四舍五入为整数，则会得到午夜时间。1900-01-01T00:00:00.000为 0。
回答by user1431616
Use following:
使用以下：
select DATEADD(SECOND, 36000, CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS datetime))
This way you can adjust the seconds to pinpoint any time of day you want.
通过这种方式，您可以调整秒数以精确定位您想要的一天中的任何时间。
Please refer to this for date/time formatting in SQL Server: SQL Server Date/Time Formatting
回答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.
希望这可以帮助某人。
