Hello,

I am concerned as similiar queries, only changing the way I specify the
dates, give different results.

Query A would use EXTRACT, example:
EXTRACT(month FROM accountingdate)=' . $currentmonth . '

Query B would use <>=, example:
accountingdate>=' . $begindate . '

Now the odd thing is that I get different results, for a sum of sales
figures. A quick check of the actual sales summed up outside of the
database reveals that Query B gives the correct answer.

Now this is a major concern as I just Query A type syntax in some other
reports. Why doesn't it work correctly? Any links to more information
about extract? The manual information suggests that Query A should be
synonymous with Query B.

Thanks for any information

sveint wrote:

> Hello,
>
> I am concerned as similiar queries, only changing the way I specify the
> dates, give different results.
>
> Query A would use EXTRACT, example:
> EXTRACT(month FROM accountingdate)=' . $currentmonth . '
>
> Query B would use <>=, example:
> accountingdate>=' . $begindate . '
>
> Now the odd thing is that I get different results, for a sum of sales
> figures. A quick check of the actual sales summed up outside of the
> database reveals that Query B gives the correct answer.

To SQL, 1-nov-xxxx (all years) would be included in QueryA result, you must also
include the year.

> Now this is a major concern as I just Query A type syntax in some other
> reports. Why doesn't it work correctly? Any links to more information
> about extract? The manual information suggests that Query A should be
> synonymous with Query B.

No, they are not synonymous. See previous...

>
> Thanks for any information
>



--
Michael Austin

sveint wrote:

> Hello,
>
> I am concerned as similiar queries, only changing the way I specify the
> dates, give different results.
>
> Query A would use EXTRACT, example:
> EXTRACT(month FROM accountingdate)=' . $currentmonth . '
>
> Query B would use <>=, example:
> accountingdate>=' . $begindate . '
>
> Now the odd thing is that I get different results, for a sum of sales
> figures. A quick check of the actual sales summed up outside of the
> database reveals that Query B gives the correct answer.
>
> Now this is a major concern as I just Query A type syntax in some other
> reports. Why doesn't it work correctly? Any links to more information
> about extract? The manual information suggests that Query A should be
> synonymous with Query B.
>
> Thanks for any information
>
forgot to also mention that should you choose to use QueryB - I would also
include "AND accountingdate<' . $enddate . '" Make sure you know the exact
timeframe you are looking for.

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html

I didn't show the full queries on purpose. I do know who to get date
ranges (extract month, year or between two dates). Extract still gives
wrong results.

I will write out an example to be clear, with the php removed, pure
SQL:

EXTRACT(month FROM accountingdate)=12 and EXTRACT(year FROM
accountingdate)=2005

vs

accountingdate>='2005-12-01' and accountingdate<='2005-12-31'

Logically the queries should be the same but the results differ (by
about 1%). So something somewhere goes wrong when I use EXTRACT.

Michael Austin wrote:
> sveint wrote:
>
> > Hello,
> >
> > I am concerned as similiar queries, only changing the way I specify the
> > dates, give different results.
> >
> > Query A would use EXTRACT, example:
> > EXTRACT(month FROM accountingdate)=' . $currentmonth . '
> >
> > Query B would use <>=, example:
> > accountingdate>=' . $begindate . '
> >
> > Now the odd thing is that I get different results, for a sum of sales
> > figures. A quick check of the actual sales summed up outside of the
> > database reveals that Query B gives the correct answer.
> >
> > Now this is a major concern as I just Query A type syntax in some other
> > reports. Why doesn't it work correctly? Any links to more information
> > about extract? The manual information suggests that Query A should be
> > synonymous with Query B.
> >
> > Thanks for any information
> >
> forgot to also mention that should you choose to use QueryB - I would also
> include "AND accountingdate<' . $enddate . '" Make sure you know the exact
> timeframe you are looking for.
>
> --
> Michael Austin.
> DBA Consultant
> Donations welcomed. Http://www.firstdbasource.com/donations.html
>