Professional Web Applications Themes

Concerned about dates [ extract vs <>= ] - MySQL

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

  1. #1

    Default Concerned about dates [ extract vs <>= ]

    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 Guest

  2. #2

    Default Re: Concerned about dates [ extract vs <>= ]

    sveint wrote:
     

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

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



    --
    Michael Austin
    Michael Guest

  3. #3

    Default Re: Concerned about dates [ extract vs <>= ]

    sveint wrote:
     
    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
    :)
    Michael Guest

  4. #4

    Default Re: Concerned about dates [ extract vs <>= ]

    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: 
    > 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
    > :)[/ref]

    sveint Guest

Similar Threads

  1. Concerned
    By Sandra in forum Macromedia Freehand
    Replies: 10
    Last Post: November 4th, 06:02 PM
  2. Replies: 4
    Last Post: August 5th, 01:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139