sql查询基于当前日期从数据库中检索数据? [英] sql query to retrieve data from database based on current date ?

问题描述

sql查询根据当前日期从数据库中检索数据?

sql query to retrieve data from database based on current date ?

推荐答案

看看您的示例，我不确定您为什么变得如此复杂.要查找将来的记录，您可以这样:

Looking at your examples, I''m not sure why you are getting so complicated. To find out the records that are in the future, you would do so like this:

SELECT *
FROM tblPhoneAsign
WHERE receivedTime > GETDATE()

如果您只关心日期而不是时间，可以这样:

If you are concerned about only having the date and not the time, you could so something like this:

SELECT *
FROM tblPhoneAsign
WHERE receivedTime > CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)

那只会给您今天日期中的日期部分.在GETDATE方法中添加零小时是一种非常复杂且成本很高的处理方式.

至于为什么当您从代码中调用该方法时该方法不起作用，我将尝试上面列出的代码.如果那不起作用，请尝试完全取消WHERE语句，然后查看是否有任何返回.如果不是，那您就知道这不是查询的问题，而是您的代码.也许您的连接字符串是错误的.

That will give you only the date portion of today''s date. Doing the adding of zero hours to the GETDATE method is a very complex and costly way of doing things.

As for why the method doesn''t work when you call it from code, I would try the code I listed above. If that doesn''t work, try knocking off the WHERE statement entirely and seeing if anything comes back. If it doesn''t, you know it isn''t the fault of the query but instead it is your code. Maybe your connection string is wrong.

Tim的回答非常好...

MS SQL Server接受许多日期和时间格式: http://msdn.microsoft.com/en-us/library/ms186724.aspx [ ^ ]取决于其版本.

因此，将时间部分添加到现有日期字段中并不一定是非常复杂且昂贵的事情.
在MS SQL Server 2012中，可以使用 DATEFROMPART [自定义函数 [^ ].

最后，要将日期时间字段转换为"00:00:00.000"作为时间部分，可以使用如下查询:

Tim''s answer is very good...

MS SQL Server accept many date and time formats: http://msdn.microsoft.com/en-us/library/ms186724.aspx[^] depends on its version.

So, adding a time part to existing date field should not be a necessary very complex and costly.
In MS SQL Server 2012, it is possible to use DATEFROMPART[^] function, which returns only date value without time part. In the previous version of MS SQL it is possible to write custom function[^].

Finally, to convert datetime field with "00:00:00.000" as time part, it''s possible to use a query like this:

SET DATEFORMAT ymd;
SELECT *
FROM tblPhoneAsign
WHERE receivedTime > CONVERT(DATETIME,CONVERT(NVARCHAR(10),GETDATE(),121))

这篇关于sql查询基于当前日期从数据库中检索数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！