sql 查询将现有表中的日期与所选日期进行比较？

发布于 2024-10-01 20:16:15 字数 152 浏览 4 评论 0原文

我需要将现有表中的日期与所选日期进行比较以确保相等。我正在开发一个asp.net应用程序，我想在已经存在的表中插入一条新记录，并且我在该asp.net表单中使用ajax日历扩展程序，我需要将所选日期与已经存在的日期进行比较，以便即使在添加新记录之前，也会在网格视图中显示包含相同日期的记录。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苏辞 2024-10-08 20:16:15

我不完全明白问题出在哪里......

DECLARE @YourDate DATETIME
SET @YourDate = '2010-11-09'

SELECT (list of columns)
FROM dbo.YourTable
WHERE SomeDateColumn = @YourDate

您需要注意的主要问题：在SQL Server 2005版本之前，您只有DATETIME数据类型，顾名思义，它存储日期和时间 - 因此精确匹配将匹配到毫秒......通常这不是您真正想要的。

您能更详细地解释一下您想要实现的目标以及遇到的麻烦吗？

I don't completely understand where the problem is....

DECLARE @YourDate DATETIME
SET @YourDate = '2010-11-09'

SELECT (list of columns)
FROM dbo.YourTable
WHERE SomeDateColumn = @YourDate

The main issue you need to be aware of: in SQL Server up to version up to version 2005, you only have the DATETIME data type, which as its name implies stores both date and time - so an exact match will match down to the millisecond.... often this is not what you really want.

Can you explain in more detail what you're trying to achieve and what the trouble is you're having??

回复收藏 0 原文

为你拒绝所有暧昧 2024-10-08 20:16:15

如果您只对 DATE 元素的相等感兴趣，忽略列值中存在的时间，则使用如下子句：

DECLARE @DateParam DATETIME
SET @DateParam = '20101109'

SELECT SomeField
FROM SomeTable
WHERE DateField >= @DateParam AND DateField < DATEADD(dd, 1, @DateParam)

此示例将查找 DateField 列在任意点具有日期（和时间）的所有记录提到的那一天。

If you're only interested in equality on the DATE element, ignoring the time present within a column value, then use a clause like this:

DECLARE @DateParam DATETIME
SET @DateParam = '20101109'

SELECT SomeField
FROM SomeTable
WHERE DateField >= @DateParam AND DateField < DATEADD(dd, 1, @DateParam)

This example would find all records where DateField column has a date(& time) at any point in the mentioned day.

回复收藏 0 原文

一城柳絮吹成雪 2024-10-08 20:16:15

我完全同意 marc_s 和 AdaTheDev 的解决方案（以及他们和我对您需要什么的理解 - 您需要在您的问题中定义您选择的日期是什么）

但是假设您选择的输入是一个日期（如其他答案中所示）格式为 yyyy-mm-dd (marc_s) 或 yyyymmdd (adathedev)，

DECLARE @Date DATETIME
SET @Date = '2010/10/10'

SELECT * 
FROM SomeTable
WHERE CONVERT(VARCHAR(10), DateField, 126) = @Date

当您声明日期时间并仅使用日期设置它时，以下替代方案也应该有效 - 时间设置为 12:00:00.000。因此，使用相等运算符，它将查找与时间完全匹配的内容（如果您使用 GETDATE() 之类的内容插入表中，则这种情况极不可能发生）。

我的解决方案只是在查询时减少数据库中字段的时间。但我仍然会选择其他解决方案。这只是一个替代方案。

给其他人+1。

I would totally go with the solutions from marc_s and AdaTheDev (and their and my understanding of what you need - you need to define in your question what your selected date is)

But assuming your selected input is a date (as in the other answers) with format of yyyy-mm-dd (marc_s) or yyyymmdd (adathedev), the following alternative should also work

DECLARE @Date DATETIME
SET @Date = '2010/10/10'

SELECT * 
FROM SomeTable
WHERE CONVERT(VARCHAR(10), DateField, 126) = @Date

When you Declare datetime and set it using only the date - the time is set to 12:00:00.000. So with an equality operator it will look for that exact match with the time (which is highly unlikely if you're inserting into the table with something like GETDATE()).

My solution simply takes the time off the fields in the database when querying. But I would still go with the other solutions. This is simply an alternative.

+1 to the other guys.

回复收藏 0 原文