CODEWITHSUNDEEP
sqlbetweenteradata
Andrew
Andrew

Reputation: 7768

SQL (Teradata) Select data

I have a questions related to SQL (teradata in particular). I have to generate the report for 1 day.

How can I achieve it?

For example, in ms access, I can do 

WHERE DT>=#2011-01-01# and DT<=#2011-0101#

What about big-guys? (SQL Server, not 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?

I apologize, I don't have the SQL access and I can't test it; therefore I am asking for professional advise.

Upvotes: 4

Views: 51271

Answers (4)

Prayson W. Daniel
Prayson W. Daniel

Reputation: 15558

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 Manual on BETWEEN

Following their manual, x BETWEEN y AND z == ((x >= y) AND (x <=z))

Upvotes: 0

Charles Burns
Charles Burns

Reputation: 10602

Working with dates in Teradata can be a little tricky.

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.

Let's say you want to report on today, so pass in '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:

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.

Upvotes: 4

Clockwork-Muse
Clockwork-Muse

Reputation: 13046

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: 

SELECT [list of columns]
FROM [table]
WHERE dt >= :startDate
AND dt < :endDate

(the :variableName is 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.

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

BETWEEN is range-inclusive, so this will do:

DT between '2011-09-01' and '2011-09-01'

And, yes, it is precise :)

Now, if your DT is a datetime field (not date field), then you must change your approach:

DT >= '2011-09-01' and DT < '2011-09-02'

Upvotes: 8

Related Questions