SQL (Teradata) Select data

Question

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.

Answer 1

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'

Answer 2

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.