SQL 帮助... CONVERT(int, CONVERT(datetime, FLOOR(CONVERT(float, getdate()))) [英] SQL HELP... CONVERT(int, CONVERT(datetime, FLOOR(CONVERT(float, getdate())))

问题描述

我在调整 SQL 语句的这一部分时遇到问题:

I am having a problem adjusting this part of my SQL statement:

HAVING dbo.BOOKINGS.BOOKED = CONVERT(int, CONVERT(datetime, 
                                                  FLOOR(CONVERT(float, GETDATE()))) + 2)

通常，使用此语句的页面仅列出今天的销售额，我想将 GETDATE() 切换到我声明的日期.我尝试了所有不同的格式，但没有一个成功

Normally, the page that uses this statement just lists the amount of sales for today, I want to switch the GETDATE() to a date that I declare. I tried all different formats and none have worked

推荐答案

使用 DATEADD/DATEDIFF 方法将时间部分设置为当前日期的午夜 - 这是最快的方法，并且转换为 FLOAT 可能不可靠:

Use the DATEADD/DATEDIFF method of setting the time portion to midnight of the current date - it's the fastest means, and casting to FLOAT can be unreliable:

HAVING BOOKINGS.dbo.BOOKED = CONVERT(INT, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))+2

然后，如果您使用变量(在本例中为@var，在存储过程或函数中)，您可以轻松设置自己的日期:

Then, you can set your own date easily if you use a variable (@var in this example, within a stored procedure or function):

DECLARE @var DATETIME

SELECT ...
HAVING BOOKINGS.dbo.BOOKED = CONVERT(INT, DATEADD(dd, DATEDIFF(dd, 0, @var), 0))+2

这假设@var 是 DATETIME 数据类型.否则，您将需要使用 SQL Server 将隐式转换为 DATETIME 的日期格式——或使用 CAST/CONVERT 显式转换值.

This assumes @var is a DATETIME data type. Otherwise, you'll need to use a date format SQL Server will implicitly convert to a DATETIME -- or use CAST/CONVERT to explicitly convert the value.

这篇关于SQL 帮助... CONVERT(int, CONVERT(datetime, FLOOR(CONVERT(float, getdate())))的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！