in SQL, i wonder if I do a


select * from tablefoo where add_date = "2007-11-01"


then it may not show any record as it will only match recorded added
exactly at 2007-11-01 00:00:00
so to limit that day, I could use

select * from tablefoo where date(add_date) = "2007-11-01"

except I think if the table has millions of records, then it can take
forever to run, as it will go through all records and apply the date
function on each record's add_date.

so the following

select * from tablefoo where add_date >= "2007-11-01" and add_date <
"2007-11-02"

should work... except it is quite verbose... i wonder if there is a
better way?

Summercool wrote:
> in SQL, i wonder if I do a
>
>
> select * from tablefoo where add_date = "2007-11-01"

this works for mysql:
SELECT * FROM tablefoo WHERE add_date>20071101 AND add_date<20071102



--

//Aho

Summercool wrote:
> in SQL, i wonder if I do a
>
>
> select * from tablefoo where add_date = "2007-11-01"
>
>
> then it may not show any record as it will only match recorded added
> exactly at 2007-11-01 00:00:00
Only if add_date is a DATETIME or TIMESTAMP type of field as oposed to a
DATE type.

"Paul Lautman" <paul.lautman@btinternet.com> wrote in message
news:5p5r0uFpgjp6U1@mid.individual.net...
> Summercool wrote:
>> in SQL, i wonder if I do a
>>
>>
>> select * from tablefoo where add_date = "2007-11-01"
>>
>>
>> then it may not show any record as it will only match recorded added
>> exactly at 2007-11-01 00:00:00
> Only if add_date is a DATETIME or TIMESTAMP type of field as oposed to a
> DATE type.
>
>

Many possibilities...

I always add a indexed DateID column that holds a FK to my Dates table. An
integer lookup will always be faster.

or

You could add (and index) a column that holds only the date portion of the
datetime field. There are many ways to truncate the time portion, I use
convert(datetime,convert(varchar(50),add_date,101) )

or

You could use ...BETWEEN '2007-11-01' AND '2007-11-02'

Try this.

select * from tablefoo
where convert(varchar, add_date, 101) = '11/01/2007'


"Summercool" <Summercoolness@gmail.com> wrote in message
news:1194176707.385279.102190@t8g2000prg.googlegro ups.com...
> in SQL, i wonder if I do a
>
>
> select * from tablefoo where add_date = "2007-11-01"
>
>
> then it may not show any record as it will only match recorded added
> exactly at 2007-11-01 00:00:00
> so to limit that day, I could use
>
> select * from tablefoo where date(add_date) = "2007-11-01"
>
> except I think if the table has millions of records, then it can take
> forever to run, as it will go through all records and apply the date
> function on each record's add_date.
>
> so the following
>
> select * from tablefoo where add_date >= "2007-11-01" and add_date <
> "2007-11-02"
>
> should work... except it is quite verbose... i wonder if there is a
> better way?
>

VC wrote:
>> in SQL, i wonder if I do a
>>
>>
>> select * from tablefoo where add_date = "2007-11-01"
>>
>>
>> then it may not show any record as it will only match recorded added
>> exactly at 2007-11-01 00:00:00
>> so to limit that day, I could use
>>
>> select * from tablefoo where date(add_date) = "2007-11-01"
>>
>> except I think if the table has millions of records, then it can take
>> forever to run, as it will go through all records and apply the date
>> function on each record's add_date.
>>
>> so the following
>>
>> select * from tablefoo where add_date >= "2007-11-01" and add_date <
>> "2007-11-02"
>>
>> should work... except it is quite verbose... i wonder if there is a
>> better way?
> Try this.
>
> select * from tablefoo
> where convert(varchar, add_date, 101) = '11/01/2007'
>
>
> "Summercool" <Summercoolness@gmail.com> wrote in message
> news:1194176707.385279.102190@t8g2000prg.googlegro ups.com...
Please do not top post - top posting fixed.

Why is your suggestion any better than the OP's date() function call?

On Nov 4, 6:45 am, Summercool <Summercooln...@gmail.com> wrote:
> in SQL, i wonder if I do a
>
> select * from tablefoo where add_date = "2007-11-01"
>
> then it may not show any record as it will only match recorded added
> exactly at 2007-11-01 00:00:00
> so to limit that day, I could use
>
> select * from tablefoo where date(add_date) = "2007-11-01"
>
> except I think if the table has millions of records, then it can take
> forever to run, as it will go through all records and apply the date
> function on each record's add_date.
>
> so the following
>
> select * from tablefoo where add_date >= "2007-11-01" and add_date <
> "2007-11-02"
>
> should work... except it is quite verbose... i wonder if there is a
> better way?

Is the ADD_DATE column defined in the database as a DATE? If so, some
people may be tempted to write:
SELECT
*
FROM
TABLEFOO
WHERE
TRUNC(ADD_DATE) = '01-NOV-2007';

The assumption of the above is that there is an index on the ADD_DATE
column that will speed data retrieval... only to find that Oracle
performs a full tablescan to identify the matching rows. A function
based index could be set up to allow the above syntax to execute
without a full tablescan, but is that the best approach? In my
opinion, I would not create another index unless there were no other
choices. For example, I would use one of the following, most likely
the first:
SELECT
*
FROM
TABLEFOO
WHERE
ADD_DATE >= '01-NOV-2007'
AND ADD_DATE < '02-NOV-2007';

SELECT
*
FROM
TABLEFOO
WHERE
ADD_DATE BETWEEN '01-NOV-2007' AND '02-NOV-2007'
AND ADD_DATE <> '02-NOV-2007';

The second predicate in the second SQL statement's WHERE clause is
necessary to prevent those matches that occur at exactly midnight on
02-NOV-2007 from being included. If ADD_DATE is a VARCHAR2 column,
either of the above methods will also work (after reformatting the
date constant), but Oracle may incorrectly predict the number of rows
that will be returned by the query, and may force a full tablescan,
even if there is an index on the ADD_DATE column - if that happens, an
INDEX hint may be used to force an index based execution plan.

In summary: The shortest programming solution may not be the most
efficient solution.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Charles Hooper schrieb:
> On Nov 4, 6:45 am, Summercool <Summercooln...@gmail.com> wrote:
>> in SQL, i wonder if I do a
>>
>> select * from tablefoo where add_date = "2007-11-01"
>>
>> then it may not show any record as it will only match recorded added
>> exactly at 2007-11-01 00:00:00
>> so to limit that day, I could use
>>
>> select * from tablefoo where date(add_date) = "2007-11-01"
>>
>> except I think if the table has millions of records, then it can take
>> forever to run, as it will go through all records and apply the date
>> function on each record's add_date.
>>
>> so the following
>>
>> select * from tablefoo where add_date >= "2007-11-01" and add_date <
>> "2007-11-02"
>>
>> should work... except it is quite verbose... i wonder if there is a
>> better way?
>
> Is the ADD_DATE column defined in the database as a DATE? If so, some
> people may be tempted to write:
> SELECT
> *
> FROM
> TABLEFOO
> WHERE
> TRUNC(ADD_DATE) = '01-NOV-2007';
>
> The assumption of the above is that there is an index on the ADD_DATE
> column that will speed data retrieval... only to find that Oracle
> performs a full tablescan to identify the matching rows. A function
> based index could be set up to allow the above syntax to execute
> without a full tablescan, but is that the best approach? In my
> opinion, I would not create another index unless there were no other
> choices. For example, I would use one of the following, most likely
> the first:
> SELECT
> *
> FROM
> TABLEFOO
> WHERE
> ADD_DATE >= '01-NOV-2007'
> AND ADD_DATE < '02-NOV-2007';
>
> SELECT
> *
> FROM
> TABLEFOO
> WHERE
> ADD_DATE BETWEEN '01-NOV-2007' AND '02-NOV-2007'
> AND ADD_DATE <> '02-NOV-2007';
>
> The second predicate in the second SQL statement's WHERE clause is
> necessary to prevent those matches that occur at exactly midnight on
> 02-NOV-2007 from being included. If ADD_DATE is a VARCHAR2 column,
> either of the above methods will also work (after reformatting the
> date constant), but Oracle may incorrectly predict the number of rows
> that will be returned by the query, and may force a full tablescan,
> even if there is an index on the ADD_DATE column - if that happens, an
> INDEX hint may be used to force an index based execution plan.
>
> In summary: The shortest programming solution may not be the most
> efficient solution.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
>

Charles, he has crossposted on all possible RDBMS newsgroups, i don't
think, there is a commons answer (in terms of performance) to his
question, first should be specified, on which RDBMS is it intended to run.

Best regards

Maxim