Re: question about subtracting dates

On Sep 11, 6:27 pm, Adam Sandler <cor..._at_excite.com> wrote:
> Hello,
>
> I'm using 10.2.0.1.0
>
> I'm maintaining app which executes this SQL:
>
> SELECT
>   TO_DATE(max(RPR_START_TIME)) - TO_DATE(max(JCN_DATE)) AS formula1
> FROM
>   [snip]
>
> Here's some supporting information:
>
> DESC ORD1TYPE_J;
>
> Name                           Null     Type
> ------------------------------ -------- ------------------------
> RPR_START_TIME                          DATE
> JCN_DATE                       DATE
>
> At any rate, there's a row where:
>
> RPR_START_TIME = 1994-11-15 00:00:00.0
> JCN_DATE  = 2094-06-11 00:00:00.0
> formula1 = 157
>
> Now, November 15 is day 315 and June 11 is day 162; 319 - 162 = 157...
> which matches my formula1 value.  But what perplexes me is the year
> for JCN_DATE is 2094.  Why didn't that get included in the
> calculation?  The value for the difference in dates should be much
> larger than 157 if one of the years is 2094.
>
> Any suggestions are greatly appreciated.
>
> Thanks!

Any reason you don't want to use oracle provided functionality like days_between? Received on Thu Sep 11 2008 - 17:46:41 CDT