List:General Discussion« Previous MessageNext Message »
From:Simon Wilkinson Date:March 16 2012 5:30pm
Subject:date comparison query
View as plain text  
Hi,

I have a table that holds two datetime columns.  I am trying to find values
from this table that fall into specific time ranges, but am getting some
strange results.  For example, if I try to find rows where the difference
between the two column is between 47 and 48 weeks, I get back a result
where the actual difference is less than 1 month.

My query for this is as follows: select * from table where table.date1 -
table.date2 between 28425600 and 29030400;

The result returns a row where date1 is 2010-10-31 18:24:49, and date2
is 2010-10-02 20:29:54.

I seem to get proper results for some values (I am trying to find results
that fall into different weekly ranges), but then some are just way off.
 Does anybody have any ideas for why this is happening?

Thanks,

Simon

Thread
date comparison querySimon Wilkinson16 Mar
  • Re: date comparison queryBaron Schwartz16 Mar
  • Re: date comparison queryhsv17 Mar
    • One inst has 39 columns- the other 40Charles Brown18 Mar
      • Re: One inst has 39 columns- the other 40Johan De Meersman19 Mar
  • One inst has 39 columns- the other 40Charles Brown19 Mar
  • RE: One inst has 39 columns- the other 40Charles Brown21 Mar
    • Re: One inst has 39 columns- the other 40Johan De Meersman21 Mar
      • Re: One inst has 39 columns- the other 40Rik Wasmus21 Mar
        • RE: One inst has 39 columns- the other 40Charles Brown22 Mar
          • Re: One inst has 39 columns- the other 40Nuno Tavares22 Mar
            • Re: One inst has 39 columns- the other 40Michael Dykman22 Mar
          • Re: One inst has 39 columns- the other 40Reindl Harald22 Mar
            • RE: One inst has 39 columns- the other 40Charles Brown22 Mar
          • Re: One inst has 39 columns- the other 40Mark Goodge22 Mar
    • Re: One inst has 39 columns- the other 40Reindl Harald21 Mar
      • tables do not have the same number of columns. Charles Brown22 Mar
        • Re: tables do not have the same number of columns.Johan De Meersman22 Mar