sql and dates query?delphi dont query dates?

[Norman O Bird]

can someone please explain how to query a specefic date. i.e all
appointments on 12/23/96. i cant seem to do it . whats the trick. this
doesnt work
select * from customer
where table1.date = '12/23/96'

or where date between 12/23/96 and 12/30/66

none of these work. h e l p

[Martin Jesterhoudt]

Norman O Bird <ga...@earthlink.net> wrote:

>can someone please explain how to query a specefic date. i.e all
>appointments on 12/23/96. i cant seem to do it . whats the trick. this
>doesnt work
> select * from customer
> where table1.date = '12/23/96'

The value is of the type String. I think (but have not tested this)
you will have to translate the string into a date with the StrToDate()
function.

[Harold]

On Wed, 15 Jan 1997 23:15:20 -0500, Norman O Bird <ga...@earthlink.net>
wrote:

>can someone please explain how to query a specefic date. i.e all
>appointments on 12/23/96. i cant seem to do it . whats the trick. this
>doesnt work
> select * from customer
> where table1.date = '12/23/96'
>

>or where date between 12/23/96 and 12/30/66
>
>none of these work. h e l p

try

select * from customer where date = "12/23/96"

Make sure that the field you are querying is actually called date.

[John Buxton]

Norman O Bird <ga...@earthlink.net> wrote:

>can someone please explain how to query a specefic date. i.e all
>appointments on 12/23/96. i cant seem to do it . whats the trick. this
>doesnt work
> select * from customer
> where table1.date = '12/23/96'
>
>or where date between 12/23/96 and 12/30/66
>
>none of these work. h e l p

date is a reserved word in SQL and can't be used in it's raw state.
The date field is of type tdatetime.
try table1."date" for the field name and either strtodate('2/23/96')
or use a parameter in the SQL query and pass the date as a tdatetime
object.
Something like:
Thisdate : tdatetime;

Thisdate := StrtoDate(edit1.text);

select * from customer where table1."date" = :mydate;

params[0].AsDateTime := Thisdate

There is an example in the mast app that is worth looking at.

.. John

-- -- -- -- -- -- -- --

Internet jo...@teletrak.com.au
Junk mail filter in the reply to address.

3.01 Years to total confusion.

(Sending any email to this site constitutes a licence to copy it to any
company which might reasonably be assumed to have transported it or
might reasonably be assumed to service any addresses quoted in it. 15 Sep 1996)

[Simon Munro]

In article <5bma8b$e...@neptune.worldonline.nl>, mar...@worldonline.nl (Martin

Jesterhoudt) wrote:
>Norman O Bird <ga...@earthlink.net> wrote:
>
>>can someone please explain how to query a specefic date. i.e all
>>appointments on 12/23/96. i cant seem to do it . whats the trick. this
>>doesnt work
>> select * from customer
>> where table1.date = '12/23/96'
>

>The value is of the type String. I think (but have not tested this)
>you will have to translate the string into a date with the StrToDate()
>function.
>

Usually when I do date SQL queries,I use parameterised queries, this gets
around any problems of individual date formats on different systems.

ie: table1.date=:qDate

paramByName('qDate').AsDateTime:=theDate

Cheers, Simon

[Ken Carlson]

I haven't tried these in Delphi but I know that they are valid
SQL queries as I run simular queries from pdoxwin 5.0 against MS-Sql
Server data regularly:

select *
from customer table1
where (datediff(dd,table1.date,convert(datetime,'12/23/96')) = 0)

or

select *
from customer table1
where date between convert(datetime,'12/23/96')
and convert(datetime,'12/30/96'))

The second options result will include 12/23/96 but not 12/30/96.
Because of the convert to datetime the query is actually saying
date > 12/23/96, 00:00:00 and < 12/30/96, 00:00:00.

Ken Carlson

Norman O Bird wrote:
>
> can someone please explain how to query a specefic date. i.e all
> appointments on 12/23/96. i cant seem to do it . whats the trick. this
> doesnt work
> select * from customer
> where table1.date = '12/23/96'
>

[Marc Scheuner]

Norman O Bird <ga...@earthlink.net> wrote:
>can someone please explain how to query a specefic date. i.e all
>appointments on 12/23/96. i cant seem to do it . whats the trick. this
>doesnt work
> select * from customer
> where table1.date = '12/23/96'

What kind of database are you using?

We use Oracle and Delphi 1, and the above query would not retrieve any
rows. Oracle stores date and time information together.

So if you want to query only on the date portion of a DATE field in
Oracle, do it like this:

select * from customer
where datefield = '12/23/96'

Also, Oracle and Delphi have to strictly agree on the format of the
date, which can be a bit tricky (as we found out the hard way). Check
to see that Oracle doesn't expect a date like '23-DEC-96'.

Marc

----------------------------------------------------------------------
Marc Scheuner, marc.s...@bernoise.ch CH-3001 BERNE, Switzerland
E-Mail address removed to fight Internet spam

[Andrew Mathenge]

Here is your query:

select * from customer
where table1.date = '12/23/96'

The reason it won't work is because the where clause does not apply a
condition to the customer table. You would like to return a result
set that is constrained by the condition.

Assuming that the "date" field is a field in the customer table, then
the following will work:

select * from customer where date = "12/23/96"

This is the same thing as:

select * from customer where customer.date = "12/23/96"

The second condition did not work because you need to put quotes
around the dates you are testing for. Again assuming that the "date"
field is a member of the customer table, then the following will work:

select * from customer

where date between "12/23/96" and "12/30/66"

Hope this helps.

Andrew