使用当前日期从表中检索数据? [英] Retrieve data from a table Using Current Date?

问题描述

我在表中有DATE列.现在我需要检索存储在DATE列中的当前日期数据.

例如；

Hi,

I have DATE column in a table.Now I need to retrieve the data of current date which is stored in DATE column.


ex;

SELECT * FROM [tablename] WHERE [date]=getdate()

推荐答案

尝试以下:

Try following:

SELECT * FROM [tablename] WHERE CONVERT(DATETIME,date,103)=CONVERT(DATETIME,GETDATE(),103)

或

OR

SELECT * FROM [tablename] WHERE LEFT(CONVERT(DATETIME,date,103),12)=LEFT(CONVERT(DATETIME,GETDATE(),103),12)

希望以上信息对您有所帮助.如果您还有其他顾虑，请告诉我.

I hope the above information will be helpful. If you have more concerns, please let me know.

问题是，这有点讨厌:

The thing is, this is a bit nasty:

SELECT * FROM myTable WHERE date>CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME

但是有效！

[edit]
似乎有一点解释:
最简单的方法是使用GETDATE返回的日期的各个部分来建立一个新日期，该日期实际上是午夜(这是上面的解决方案所做的事情).问题是，这需要三个GETDATE调用，并且(如果操作在错误的时间执行)(取决于SQL服务器上设置的日期和时间)可能在不同的天，月甚至几年. 即使这样，您也无法将其相等性与存储的日期进行比较，因为存储的日期很有可能也具有时间成分.
因此，它的作用是:
1)获取当前日期和时间.
2)将其转换为浮点数(已经为所有实际用途所用).自参考日期以来的天数是整数部分，自午夜以来的时间是小数部分.
3)删除小数部分，将其设置为午夜.
4)将其转换回日期时间，以便可以在比较中使用.
5)仅选择自最后一个午夜以来具有日期时间的那些记录.

我告诉过你这太讨厌了！
OriginalGriff [/edit]

But it works!

[edit]
A little explanation seems to be in order:
The easiest way is to use the various parts of the date returned by GETDATE to build a new date, that is effectively at midnight (which is what the solution above does). The problem is, that that requires three GETDATE calls, and (depending on the date and time set on your SQL server) could be in different days, months, or even years if the operation is executed at the wrong time.
Even then, you can''t compare this for equality to a stored date, because there is a good chance the stored date has a time component as well.
So, what it does:
1) Gets the current date and time.
2) Converts it to a float (which it is, already for all practical purposes). The days-since-the-reference-date number is in the integer part, the time-since-midnight is in the fractional.
3) Removes the fractional part, which sets it to midnight.
4) Converts it back to a datetime so it can be used in a comparison.
5) Selects only those records with a datetime since the last midnight.

I told you it was nasty!
OriginalGriff[/edit]

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