you should use a variable instead of hardcoding the date as I have done.
SELECT ADD_MONTHS (TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy'), 12)
- TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy') -- days in
this year
- CASE
WHEN ADD_MONTHS (TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'),
'yyyy'), 2) =
TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy') +
60 -- this is a leap year
AND TO_DATE ('15/02/2012', 'dd/mm/yyyy') <
--
TRUNC (TO_DATE ('15/02/2012', 'dd/mm/yyyy'), 'yyyy')
+ 59 -- target date is before feb 29
THEN
1
ELSE
0
END
days
FROM DUAL;
On Tue, May 31, 2011 at 10:56 AM, Yuvaraj Sundaresan <
[email protected]> wrote:
> Normally 2012 is a leap year so the total number of days will be 366 but
> according to our input the To date doesnt fall after 29/02/2012 so it should
> return 365.
>
>
> On Tue, May 31, 2011 at 10:30 AM, Yuvaraj Sundaresan <
> [email protected]> wrote:
>
>> 2012 should be 365 because he to date is '15/02/2012' doesnt fall after
>> 29/02/2011
>>
>>
>> On Tue, May 31, 2011 at 10:27 AM, Michael Moore
>> <[email protected]>wrote:
>>
>>> Which year, 2012 or 2011?
>>>
>>>
>>> On Tue, May 31, 2011 at 10:18 AM, Yuvaraj Sundaresan <
>>> [email protected]> wrote:
>>>
>>>> Hi Michel
>>>> 365 is not the difference of two dates total number of days of that
>>>> year
>>>>
>>>>
>>>> On Tue, May 31, 2011 at 10:03 AM, Michael Moore <
>>>> [email protected]> wrote:
>>>>
>>>>> How is that 365 days?
>>>>>
>>>>> SQL> SELECT TO_DATE ('15/02/2012', 'dd/mm/yyyy')
>>>>> - TO_DATE ('01/04/2011', 'dd/mm/yyyy') days
>>>>> FROM DUAL
>>>>>
>>>>> DAYS
>>>>> ----------
>>>>> 320
>>>>> 1 row selected.
>>>>>
>>>>> Looks like 320 days to me.
>>>>>
>>>>> Mike
>>>>>
>>>>>
>>>>>
>>>>> On Tue, May 31, 2011 at 9:45 AM, Yuvaraj Sundaresan <
>>>>> [email protected]> wrote:
>>>>>
>>>>>> Hi Friends,
>>>>>> I need to print the no of days(365/366) based on my input. For
>>>>>> example
>>>>>>
>>>>>> * From Date To Date Output Should Be *
>>>>>> (Input 1) (Input 2)
>>>>>> 01/04/2011 31/03/2012 366
>>>>>> 01/04/2011 15/02/2012 365(Since the To date is falling
>>>>>> in Leap year but the To date is less than 29/FEB/2012 so it should print
>>>>>> as
>>>>>> *365*)
>>>>>> 01/04/2012 31/03/2013 365
>>>>>> 01/01/2012 30/07/2012 366
>>>>>>
>>>>>> Kindly suggest me how it can be achievable.
>>>>>>
>>>>>>
>>>>>> Regards
>>>>>> Yuvaraj
>>>>>>
>>>>>> --
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "Oracle PL/SQL" group.
>>>>>> To post to this group, send email to [email protected]
>>>>>> To unsubscribe from this group, send email to
>>>>>> [email protected]
>>>>>> For more options, visit this group at
>>>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>>>>
>>>>>
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "Oracle PL/SQL" group.
>>>>> To post to this group, send email to [email protected]
>>>>> To unsubscribe from this group, send email to
>>>>> [email protected]
>>>>> For more options, visit this group at
>>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>>>
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "Oracle PL/SQL" group.
>>>> To post to this group, send email to [email protected]
>>>> To unsubscribe from this group, send email to
>>>> [email protected]
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Oracle PL/SQL" group.
>>> To post to this group, send email to [email protected]
>>> To unsubscribe from this group, send email to
>>> [email protected]
>>> For more options, visit this group at
>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>
>>
>>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
