如何在查询中排除状态为 Pending 且创建日期小于当前日期的行? [英] How can I exclude in my query that rows that have status is Pending and the Creation Date is less than the current date?

问题描述

如何在查询中排除状态为 Pending 且创建日期小于当前日期的行?

How can I exclude in my query that rows that have status is Pending and the Creation Date is less than the current date ?

[Table]  
Status CreationDate  
Pending  9/30/2014 9:00 PM
Completed 9/14/2014 10:52 AM
Pending 9/30/2014 10:00 PM

推荐答案

SELECT * FROM sometable 
WHERE Status != 'Pending' 
AND DATE(CreationDate) = GETDATE();

由于创建日期是时间戳或日期时间字段，因此您希望将其强制转换为日期，因此您可以与 GetDate 返回的值进行比较，后者返回日期，而不是日期时间.因此，此查询仅包括状态为非待处理且创建日期为今天的记录，这与您的排除语句相反.

As you creation date is a timestamp or datetime field, you want to cast this to Date, so you can compare with the value returned by GetDate which returns a date, not datetime. So, this query only includes those records where status is not pending and the creation date is today, which is the opposite of your exclusion statement.

我无法让您成为SQL Fiddle，因为 SQL Fiddle 不支持 Sybase.

I could not make you a SQL Fiddle of this, as SQL Fiddle does not support Sybase.

注意，转换到日期在 Sybase 中被认为是 Sargable，这意味着它将有效利用在您的创建日期字段上的索引 - 您应该拥有我您有非平凡的表大小并计划大量运行此类查询.

Note, casting to date is considered Sargable in Sybase, which means it will make efficient use of an index on your creationdate field -- which you should have I you have non-trivial table sizes and plan to run this kind of query a lot.

这篇关于如何在查询中排除状态为 Pending 且创建日期小于当前日期的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！