Board index » delphi » DateTime Compare with Oracle 7.3.3

DateTime Compare with Oracle 7.3.3

Dear All:
  I try to write an SQL to select a range of the date for example.
  Select * from foo where create_date between :d1 and :d2.
  It does not work.
  P.S. I use D3 and Oracle 7.3.3 NT
  Any body has idea?

--
George Huang
At Accton Technology

 

Re:DateTime Compare with Oracle 7.3.3


Try this SQL:

Select * from foo where to_date(create_date, 'dd.mm.yy hh24:mi:ss') between
:d1 and :d2.

Oleg O. Moiseenko

Re:DateTime Compare with Oracle 7.3.3


what kind of type did you assign to you parameters ?
you should use tdatetime type !?

On Mon, 23 Feb 1998 09:51:48 +0800, "khuang" <khu...@accton.com.tw>
wrote:

Quote
>Dear All:
>  I try to write an SQL to select a range of the date for example.
>  Select * from foo where create_date between :d1 and :d2.
>  It does not work.
>  P.S. I use D3 and Oracle 7.3.3 NT
>  Any body has idea?

Christian Kaas, c.k...@odn.de
Quote
>Software-, Projektrealisierung u. Beratung
>640kB ought to be enough memory ! - Bill Gates 1981

Re:DateTime Compare with Oracle 7.3.3


On Mon, 23 Feb 1998 09:51:48 +0800, "khuang"

Quote
<khu...@accton.com.tw> wrote:
>Dear All:
>  I try to write an SQL to select a range of the date for example.
>  Select * from foo where create_date between :d1 and :d2.
>  It does not work.
>  P.S. I use D3 and Oracle 7.3.3 NT
>  Any body has idea?

You might try
select *
  from foo
 where trunc(create_date) between trunc(:d1) and trunc(:d2);

Iman L. Crawford
g...@arn.net

'Cthulu for president in '98'
'Why choose the lesser evil?'

Re:DateTime Compare with Oracle 7.3.3


Quote
Oleg O. Moiseenko wrote:
> Try this SQL:

> Select * from foo where to_date(create_date, 'dd.mm.yy hh24:mi:ss')
> between
> :d1 and :d2.

If create_date is a date field, the converting isn't necessary.
Moreover, you disable Oracle to use an index for the selection on
create_date.

Better use the following statement:
select * from FOO where CREATE_DATE between to_date(:D1, 'dd.mm.yy
hh24:mi:ss') and to_date(:D2, 'dd.mm.yy hh24:mi:ss')
The parameters D1 and D2 have to be strings like that: '23.02.98
10:15:33'

Bye
Thomas

Re:DateTime Compare with Oracle 7.3.3


Quote
khuang wrote:
> Dear All:
>   I try to write an SQL to select a range of the date for example.
>   Select * from foo where create_date between :d1 and :d2.
>   It does not work.
>   P.S. I use D3 and Oracle 7.3.3 NT
>   Any body has idea?

> --
> George Huang
> At Accton Technology

I'm not sure BETWEEN is proper sql ?

Use ...where (TrnDate >= "12/06/1997") and (TrnDate<= "26/06/1997")...
or...
 ...where (TrnDate >= :DateFrom) and (TrnDate<= :DateTo)...

Also check the data type of DateFrom.

Kind Regards
Max

--

=======================
MBS Software Co., Ltd.
M.-Ph. Blickenstorfer
67/359 Mooban Tantong 5
Phuket 83000
Thailand
Tel/Fax : +66 76 242 516
e-mail   :  m...@loxinfo.co.th
=======================

Re:DateTime Compare with Oracle 7.3.3


Quote
khuang wrote:

> Dear All:
>   I try to write an SQL to select a range of the date for example.
>   Select * from foo where create_date between :d1 and :d2.
>   It does not work.
>   P.S. I use D3 and Oracle 7.3.3 NT
>   Any body has idea?

> --
> George Huang
> At Accton Technology

Part of the problem is that Oracle dates use an implicit time value, and
this will be 0:0:0.  We've found that we have to add 1 day to the end
date, but if you do have dates with a zero time you might need to change
to a < construct.

If you always use date only to set the dates, then the time part will
always be 0:0:0 and there shouldn't be a problem

I'm assuming that you are using the construct
   parambyname('d1').AsDateTime:=
which will cause delphi to put in the appropriate syntax:
   to_date(.....)

If you set it AsString then the date comparisons won't work correctly (I
think oracle does a coversion to string and compares the character
strings)

ciao
David

+----------------------------------------------------------+
|  David Noakes            _______                         |
|  Senior Consultant         | |  |          _|_      _    |
|  dav...@thentec.com.au     | |  |/| /_\ |\  |  /_\ /     |
|                            |/   | | \_  | | \_ \_  \_    |
|  Phone +61 7 3371 6611     THENTEC PTY. LIMITED          |
|  Fax   +61 7 3371 6633     http:\\www.thentec.com.au     |
+----------------------------------------------------------+

Other Threads