将 SQL 日期时间四舍五入到午夜

我的 SQL 查询有一个小问题。我正在使用 GETDATE 函数，但是，假设我在下午5点执行脚本，它将在12/12/2011下午5点到12/18/2011下午5点之间调出记录。我怎样才能让它调出整个12/12/2011-12/18/2011的记录基本上忽略了时间。

我的剧本:

WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate > (GETDATE()-6)
183975 次浏览
小开

I usually do

SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR, MyTable.dateField, 101) = CONVERT(VARCHAR, GETDATE(), 101)

if you are using SQL SERVER 2008, you can do

SELECT *
FROM MyTable
WHERE CAST(MyTable.dateField AS DATE) = CAST(GETDATE() AS DATE)

Hope this helps

小开
最佳答案

In SQL Server 2008 and newer you can cast the DateTime to a Date, which removes the time element.

WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate >= (cast(GETDATE()-6 as date))

In SQL Server 2005 and below you can use:

WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate >= DateAdd(Day, Datediff(Day,0, GetDate() -6), 0)
小开

As @BassamMehanni mentioned, you can cast as DATE in SQL Server 2008 onwards...

SELECT
*
FROM
yourTable
WHERE
dateField >= CAST(GetDate() - 6 AS DATE)
AND dateField <  CAST(GetDate() + 1 AS DATE)

The second condition can actually be just GetDate(), but I'm showing this format as an example of Less Than DateX to avoid having to cast the dateField to a DATE as well, thus massively improving performance.


If you're on 2005 or under, you can use this...

SELECT
*
FROM
yourTable
WHERE
dateField >= DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) - 6, 0)
AND dateField <  DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) + 1, 0)
小开

Here is the simplest thing I've found

-- Midnight floor of current date


SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))

The DATEDIFF returns the integer number of days before or since 1900-1-1, and the Convert Datetime obligingly brings it back to that date at midnight.

Since DateDiff returns an integer you can use add or subtract days to get the right offset.

SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()) + @dayOffset)

This isn't rounding this is truncating...But I think that is what is being asked. (To round add one and truncate...and that's not rounding either, that the ceiling, but again most likely what you want. To really round add .5 (does that work?) and truncate.

It turns out you can add .5 to GetDate() and it works as expected.

-- Round Current time to midnight today or midnight tomorrow


SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE() + .5))

I did all my trials on SQL Server 2008, but I think these functions apply to 2005 as well.

小开 
SELECT getdate()

Result: 2012-12-14 16:03:33.360

SELECT convert(datetime,convert(bigint, getdate()))

Result 2012-12-15 00:00:00.000

小开

Try using this.

WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate >= CONVERT(DATE, GETDATE())
小开 
--
-- SQL DATEDIFF getting midnight time parts
--
SELECT GETDATE() AS Now,
Convert(DateTime, DATEDIFF(DAY, 0, GETDATE())) AS MidnightToday,
Convert(DateTime, DATEDIFF(DAY, -1, GETDATE())) AS MidnightNextDay,
Convert(DateTime, DATEDIFF(DAY, 1, GETDATE())) AS MidnightYesterDay
go
Now                   MidnightToday          MidnightNextDay        MidnightYesterDay
--------------------  ---------------------  ---------------------  ---------------------
8/27/2014 4:30:22 PM  8/27/2014 12:00:00 AM  8/28/2014 12:00:00 AM  8/26/2014 12:00:00 AM
小开

This might look cheap but it's working for me

SELECT CONVERT(DATETIME,LEFT(CONVERT(VARCHAR,@dateFieldOrVariable,101),10)+' 00:00:00.000')

小开

You could round down the time.

Using ROUND below will round it down to midnight.

WHERE Orders.OrderStatus = 'Shipped'
AND Orders.ShipDate >  CONVERT(datetime, (ROUND(convert(float, getdate()-6.5),0)))
小开

You can convert the datetime to a date then back to a datetime. This will reset the timestamp.

select getdate() --2020-05-05 13:53:35.863


select cast(cast(GETDATE() as date) as datetime) --2020-05-05 00:00:00.000
小开

Hi Try to use below query

select * from table where column_name <=(cast(GETDATE() as date)) and column_name >=(cast(GETDATE()-1 as date))

提交答案