Board index » delphi » sql and dates query?delphi dont query dates?

sql and dates query?delphi dont query dates?

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

 

Re:sql and dates query?delphi dont query dates?


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

Quote
>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.

Re:sql and dates query?delphi dont query dates?


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

Quote
>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.

Re:sql and dates query?delphi dont query dates?


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

Quote
>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  j...@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)

Re:sql and dates query?delphi dont query dates?


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

Quote
Jesterhoudt) wrote:
>Norman O Bird <g...@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

Re:sql and dates query?delphi dont query dates?


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

Quote
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'

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

> none of these work. h   e    l    p

Re:sql and dates query?delphi dont query dates?


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

Quote
>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.scheu...@bernoise.ch    CH-3001 BERNE, Switzerland
E-Mail address removed to fight Internet spam

Re:sql and dates query?delphi dont query dates?


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

Other Threads