On Sep 8, 6:40 am, Mayank kashyap <mayank.del...@gmail.com> wrote:
> Hi z1hou1
> Hope This will Be the Solution for ur desired Result.
>
> If i am wrong Pls correct me.
>
> SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||:yr, 'ddmmyy'), 'day'),
>
> 'sunday', TO_DATE ('0101' ||:yr, 'ddmmyy'),
>
> NEXT_DAY (TO_DATE ('0101' ||:yr, 'ddmmyy'), 'sunday')
>
> )
>
> FROM DUAL;
>
> Regards
>
> Mayank
>
>
>
> On Tue, Sep 8, 2009 at 4:57 PM, z1hou1 <z1h...@gmail.com> wrote:
>
> > Hi Mayank,
> > Your solution is the simplest, but there is an issue with 2006. For
> > 2006, 1-jan-2006 falls on a Sunday. So, if we are looking a Sunday
> > AFTER the 1st of the year, your solution is without doubt the easiest.
>
> > z1hou1- Hide quoted text -
>
> - Show quoted text -
You cannot get past years with the code posted, as tne YY format
specifier will take '86' and make it '2086', not '1986':
Enter value for yr: 86
old 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||&&yr, 'ddmmyy'),
'day'),
new 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||86, 'ddmmyy'),
'day'),
old 2: 'sunday', TO_DATE ('0101' ||&&yr, 'ddmmyy'),
new 2: 'sunday', TO_DATE ('0101' ||86, 'ddmmyy'),
old 3: NEXT_DAY (TO_DATE ('0101' ||&&yr, 'ddmmyy'), 'sunday')
new 3: NEXT_DAY (TO_DATE ('0101' ||86, 'ddmmyy'), 'sunday')
DECODE(TO_CHAR(TO_DA
--------------------
06-JAN-2086 00:00:00
Changing the code a bit to use the RR format provides the proper
answer for any 2-digit year:
SQL> SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||&&yr, 'ddmmrr'),
'day'),
2 'sunday', TO_DATE ('0101' ||&&yr, 'ddmmrr'),
3 NEXT_DAY (TO_DATE ('0101' ||&&yr, 'ddmmrr'), 'sunday')
4 )
5 FROM DUAL;
Enter value for yr: 86
old 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||&&yr, 'ddmmrr'),
'day'),
new 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||86, 'ddmmrr'),
'day'),
old 2: 'sunday', TO_DATE ('0101' ||&&yr, 'ddmmrr'),
new 2: 'sunday', TO_DATE ('0101' ||86, 'ddmmrr'),
old 3: NEXT_DAY (TO_DATE ('0101' ||&&yr, 'ddmmrr'), 'sunday')
new 3: NEXT_DAY (TO_DATE ('0101' ||86, 'ddmmrr'), 'sunday')
DECODE(TO_CHAR(TO_DA
--------------------
05-JAN-1986 00:00:00
SQL> undefine yr
SQL> /
Enter value for yr: 99
old 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||&&yr, 'ddmmrr'),
'day'),
new 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||99, 'ddmmrr'),
'day'),
old 2: 'sunday', TO_DATE ('0101' ||&&yr, 'ddmmrr'),
new 2: 'sunday', TO_DATE ('0101' ||99, 'ddmmrr'),
old 3: NEXT_DAY (TO_DATE ('0101' ||&&yr, 'ddmmrr'), 'sunday')
new 3: NEXT_DAY (TO_DATE ('0101' ||99, 'ddmmrr'), 'sunday')
DECODE(TO_CHAR(TO_DA
--------------------
03-JAN-1999 00:00:00
SQL> undefine yr
SQL> /
Enter value for yr: 06
old 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||&&yr, 'ddmmrr'),
'day'),
new 1: SELECT DECODE (TO_CHAR (TO_DATE ('0101' ||06, 'ddmmrr'),
'day'),
old 2: 'sunday', TO_DATE ('0101' ||&&yr, 'ddmmrr'),
new 2: 'sunday', TO_DATE ('0101' ||06, 'ddmmrr'),
old 3: NEXT_DAY (TO_DATE ('0101' ||&&yr, 'ddmmrr'), 'sunday')
new 3: NEXT_DAY (TO_DATE ('0101' ||06, 'ddmmrr'), 'sunday')
DECODE(TO_CHAR(TO_DA
--------------------
08-JAN-2006 00:00:00
SQL>
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---