I've got to be missing something obvious here.  Shouldn't this return records with the
date of Feb, 7, 2010?  Instead it returns nothing.

select * from contacts
where ctDateCreated >= DATE '2010-02-07' and ctDateCreated <= DATE '2010-02-07'

Dale

What's the data type for the crDateCreated?  Is it "Date" or is it something
like Timestamp ?

Raul


"Dale Derix" <dale@emerald-data.com> wrote in message
news:1D517277-03AC-4618-9B30-867256D8E9ED@news.elevatesoft.com...
> I've got to be missing something obvious here.  Shouldn't this return
> records with the
> date of Feb, 7, 2010?  Instead it returns nothing.
>
> select * from contacts
> where ctDateCreated >= DATE '2010-02-07' and ctDateCreated <= DATE
> '2010-02-07'
>
> Dale
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4845 (20100207) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4845 (20100207) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

<<<< "Raul" wrote:

What's the data type for the crDateCreated?  Is it "Date" or is it something
like Timestamp ?

Raul
>>>>>>>

Ahhh... that's it!

Changed it to this and it now works.

select * from contacts
where ctDateCreated >= TIMESTAMP '2010-02-07 00:00' and ctDateCreated <= TIMESTAMP
'2010-02-07 23:59'

Definitely a newbie error.

Thanks,

Dale

No problem.

If you want to avoid having to include the time every time then either
separate the timestamp into 2 columns : one for date and one for time (it is
extra work on updating them but you can have edb update them thru triggers
for example automatically) or try using next day in SQL with the strict less
than comparison (e.g. where ctDateCreated >= DATE '2010-02-07' and
ctDateCreated < DATE '2010-02-07' ).

Raul


"Dale Derix" <dale@emerald-data.com> wrote in message
news:18FF132F-512A-4D9F-B078-D501EDF9AAFA@news.elevatesoft.com...
> <<<< "Raul" wrote:
>
> What's the data type for the crDateCreated?  Is it "Date" or is it
> something
> like Timestamp ?
>
> Raul
>>>>>>>>
>
> Ahhh... that's it!
>
> Changed it to this and it now works.
>
> select * from contacts
> where ctDateCreated >= TIMESTAMP '2010-02-07 00:00' and ctDateCreated <=
> TIMESTAMP
> '2010-02-07 23:59'
>
> Definitely a newbie error.
>
> Thanks,
>
> Dale
>
>

Dale


OR

select * from contacts
where CAST(ctDateCreated AS DATE) = DATE '2010-02-07'

Roy Lambert [Team Elevate]

<<<<<
 select * from contacts
 where CAST(ctDateCreated AS DATE) = DATE '2010-02-07'

 Roy Lambert [Team Elevate]
>>>>>

Hi Roy:

Actually, the query allows a user to seach for contacts by a range of dates..... My
surprise was when it didnt work when the start and end dates were the same.  I thought
about a solution such as yours but then I would have two queries, one if the start and end
date was the same date, and the other for a daterange situation.  The value is actually
stored as a TimeStamp, so I changed the query to search from 12:00 am on the start day to
midnight on the end day.  Seems to be working fine now.

Dale