SQL (Teradata) 选择数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9789948/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL (Teradata) Select data
提问by Andrew
I have a questions related to SQL (teradata in particular). I have to generate the report for 1 day.
我有一个与 SQL（特别是 Teradata）相关的问题。我必须生成 1 天的报告。
How can I achieve it?
我怎样才能实现它？
For example, in ms access, I can do
例如，在ms access中，我可以做到
WHERE DT>=#2011-01-01# and DT<=#2011-0101#
What about big-guys? (SQL Server, not MS Access).
大佬们呢？（SQL Server，而不是 MS Access）。
I know that it is possible to use
我知道可以使用
DT between '2011-09-01' and '2011-09-02'
But this method is not precise. How can I specify 1 day using ranged WHERE statement?
但这种方法并不精确。如何使用 ranged WHERE 语句指定 1 天？
I apologize, I don't have the SQL access and I can't test it; therefore I am asking for professional advise.
我很抱歉，我没有 SQL 访问权限，无法对其进行测试；因此，我寻求专业建议。
回答by Adriano Carneiro
BETWEENis range-inclusive, so this will do:
BETWEEN是包含范围的，所以这样做：
DT between '2011-09-01' and '2011-09-01'
And, yes, it is precise :)
而且，是的，它是精确的:)
Now, if your DTis a datetime field (not date field), then you must change your approach:
现在，如果您DT是日期时间字段（不是日期字段），那么您必须更改您的方法：
DT >= '2011-09-01' and DT < '2011-09-02'
回答by Charles Burns
Working with dates in Teradata can be a little tricky.
在 Teradata 中处理日期可能有点棘手。
If DT is a "timestamp" field, you can simply convert it to a date and, because you are reporting for exactly one day, just test for equality.
如果 DT 是一个“时间戳”字段，您可以简单地将其转换为日期，并且因为您报告的正好是一天，所以只需测试是否相等。
Let's say you want to report on today, so pass in '03/20/2012':
假设您要报告今天，因此请传入“03/20/2012”：
-- Teradata: Select records where DT matches a certain day.
SELECT * -- etc...
WHERE CAST(DT as date) = TO_DATE('03/20/2012', 'mm/dd/yyyy')
MS SQL is similar:
MS SQL 类似：
SELECT * from [webdb].[mediaguide].[fileDirectories]
WHERE CAST('03/20/2012' AS date) = CAST(DT AS date)
Technically I'd use parameterization for passing in the date, but you get the idea.
从技术上讲，我会使用参数化来传递日期，但您明白了。
回答by Clockwork-Muse
When selecting over a range (especially dates and timestamps), it's best to do lower-bound inclusive, upper-bound exclusive. That is, you want things in the range lb <= x < ub. In your case, this amounts to:
在选择范围内（特别是日期和时间戳）时，最好做较低限制的包容性，上限。也就是说，你想要范围内的东西lb <= x < ub。在您的情况下，这相当于：
SELECT [list of columns]
FROM [table]
WHERE dt >= :startDate
AND dt < :endDate
(the :variableNameis how I input host variables on my system. You'll have to look up what it is on teradata.)
The strings you have listed for your between will work as-is - I think pretty much every major RDBMS recognizes *ISO formatting by default.
（这:variableName就是我在系统上输入主机变量的方式。您必须在 teradata 上查找它的内容。）
您为两者之间列出的字符串将按原样工作 - 我认为几乎每个主要 RDBMS 都识别 *ISO默认格式化。
回答by Prayson W. Daniel
Simple answer would be:
简单的答案是：
WHERE DT BETWEEN Date_X AND Date_X + 1
If you want to be explicitly
如果你想明确
WHERE DT BETWEEN Date_X AND Date_X + INTERVAL '1' DAY
You can always read Teradata Manual :)
您可以随时阅读 Teradata 手册 :)
Following their manual, x BETWEEN y AND z== ((x >= y) AND (x <=z))
按照他们的手册，x BETWEEN y AND z== ((x >= y) AND (x <=z))
