从sql中的日期时间获取日期 [英] To get date from datetime in sql

问题描述

我在表中有 datecreated 字段.它包含的值为2009-12-30 11:47:20:297"我有一个这样的查询:

I have datecreated field in a table. It contains value as "2009-12-30 11:47:20:297" I have a query like this:

select * 
  from table 
 where DateCreated = getdate()

尽管今天的日期存在一行，但在执行上述查询时我没有得到该行.有人可以帮忙吗?

Although one row exists with today's date, I am not getting that row while executing above query. Can anybody help?

推荐答案

您的查询没有返回您期望的行的原因是因为 GETDATE() 返回了执行查询时的日期和时间部分.DateCreated 列中的值与时间部分不匹配，因此不会返回任何行.

The reason why your query doesn't return the row you expect, is because GETDATE() returns the date and time portion at the moment the query was executed. The value in your DateCreated column will not match the time portion, so no rows are returned.

有多种方法可以构建查询，以便它仅根据日期组件来评估日期.下面是一个例子:

There are various ways to construct a query so that it evaluates the date based on only the date component. Here's one example:

WHERE YEAR(datecreated) = YEAR(GETDATE())
  AND MONTH(datecreated) = MONTH(GETDATE())
  AND DAY(datecreated) = DAY(GETDATE())

不幸的现实是，任何在列上使用函数的查询都意味着如果该列上存在索引，则无法使用它.

The unfortunate reality is that any query using a function on the column means that if an index exists on the column, it can't be used.

这篇关于从sql中的日期时间获取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！