SQL 获取时间为 23:59:59 的 DateTime
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6770908/
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 DateTime with time as 23:59:59
提问by Matt
I'm trying to do a where statement that specifies a DateTime field is between the start and end of the previous month.
我正在尝试执行一个 where 语句，指定 DateTime 字段位于上个月的开始和结束之间。
To do this, I need to specify that the first day of the previous month has a time of 00:00:00 and the last day of the previous month has a time of 23:59:59.
为此，我需要指定上个月的第一天的时间为 00:00:00，上个月的最后一天的时间为 23:59:59。
This second condition is giving me a headache..
这第二个条件让我头疼..
Can someone help me out?
有人可以帮我吗？
Cheers
干杯
MSSQL 2008
MSSQL 2008
回答by Oscar Gomez
try:
尝试：
SELECT DATEADD(ms, -3, '2011-07-20')
This would get the last 23:59:59 for today.
这将获得今天的最后 23:59:59。
why 3 milliseconds?, this is because Microsoft SQL Server DATETIME columns have at most a 3 millisecond resolution (something that is not going to change). So all we do is subtract 3 milliseconds
为什么是 3 毫秒？这是因为 Microsoft SQL Server DATETIME 列的分辨率最多为 3 毫秒（不会改变）。所以我们所做的就是减去 3 毫秒
回答by ysrb
You can also use the less than '<' without the equal. So that you don't need 23:59:59.
您也可以使用小于 '<' 而不使用等号。这样你就不需要 23:59:59。
Eg. WHERE DateCreated < '20111201 00:00:00'
例如。WHERE DateCreated < '20111201 00:00:00'
回答by simsim
Try this, it could be helpful for you I use one of these two ways to work with time portion in DATETIME fields to do comparisons EX: get a user log for one day, i.e. from Today's date at 12:00:00 AM till Today's date but at 12:00:00 PM
试试这个，它可能对你有帮助我使用这两种方法之一来处理 DATETIME 字段中的时间部分来进行比较 EX：获取一天的用户日志，即从今天上午 12:00:00 到今天日期，但在 12:00:00 PM
DECLARE @FromDate datetime
DECLARE @ToDate datetime
SET @FromDate = GETDATE()
SET @ToDate = GETDATE()
Print '------------------------ '
PRINT @FromDate
PRINT @ToDate
SET @FromDate = CONVERT(DATETIME, CONVERT(varchar(11),@FromDate, 111 ) + ' 00:00:00', 111)
SET @ToDate = CONVERT(DATETIME, CONVERT(varchar(11),@ToDate, 111 ) + ' 23:59:59', 111)
Print '------------------------ '
PRINT @FromDate
PRINT @ToDate
DECLARE @TEST_FROM DATETIME
SET @TEST_FROM = dateadd(month,((YEAR(@FromDate)-1900)*12)+MONTH(@FromDate)-1,DAY(@FromDate)-1) + ' 12:00:00'
DECLARE @TEST_TO DATETIME
SET @TEST_TO = dateadd(month,((YEAR(@ToDate)-1900)*12)+MONTH(@ToDate)-1,DAY(@ToDate)-1) + ' 23:59:59'
Print '------------------------ '
PRINT @TEST_FROM
PRINT @TEST_TO
This will print the following in SQL Query editor screen
这将在 SQL 查询编辑器屏幕中打印以下内容
------------------------
Dec 28 2011 3:18PM
Dec 28 2011 3:18PM
------------------------
Dec 28 2011 12:00AM
Dec 28 2011 11:59PM
------------------------
Dec 28 2011 12:00PM
Dec 28 2011 11:59PM
References The way using the convert is from my experience, the other way is from this link http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspxHave fun :)
参考 使用convert的方式是我的经验，另一种方式是来自这个链接http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx玩得开心:)
回答by Developer_29
Try this query for using Datetime datatype to get
尝试使用 Datetime 数据类型获取此查询
2018-01-29 23:59:59.997
2018-01-29 23:59:59.997
select dateadd(ms, -3, (dateadd(day, +1, convert(varchar, GETDATE(), 101))))
回答by Xile
declare @myDate DateTime, @lastMonth DateTime, @thisMonth DateTime
set @myDate = GETDATE()
set @lastMonth = DateAdd(month, -1, CAST(@myDate as Date))
set @thisMonth = DateAdd(day, -DatePart(day, @myDate)+1, CAST(@myDate as Date))
select @myDate as MyDate, DateAdd(day, -DatePart(day, @lastMonth) + 1, @lastMonth) FirstDay, DateAdd(second, -1, @thisMonth) LastDay
Results
结果
回答by Abd Al-Kareem Attiya
Try This:
尝试这个：
SELECT dateadd(millisecond,-1,cast(cast(getdate() AS date) AS datetime2))
