1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Useful Date related Queries

Discussion in 'SQL PL/SQL' started by sambuduk, Apr 2, 2013.

  1. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Dudes,

    Here I am posting some Date's related queries. These may be useful for You...

    -- To get the first day of the month
    SELECT TRUNC(SysDate,'MONTH') "Month First Day" FROM Dual;

    -- To get The last day of the month
    SELECT last_day(SysDate) "Month Last Day" FROM Dual;


    -- To get the first day of the Year
    SELECT TRUNC(SysDate,'YEAR') "Year First Day" FROM Dual;

    -- To get The last day of the year
    select ADD_MONTHS(trunc(SYSDATE,'YEAR'),12)-1 "Year Last Day" from dual


    -- To display Month start date and end Date upto last month of the year
    SELECT ADD_MONTHS(trunc:)d1,'MONTH'),i) strt_date
    ,LAST_DAY(ADD_MONTHS:)d1,i)) end_date
    FROM XMLTABLE('for $i in 0 to xs:int(D) return $i' passing xmlelement(d, floor(months_between(ADD_MONTHS(trunc:)d1,'YEAR')-1,12),:d1)) )
    columns i integer path '.')




    Regards
    Sambasiva Reddy.K
    samba.komma@gmail.com
     

    Attached Files:

    Bharat likes this.
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I can expect that you've tested these statements and found them to work, even outside of PL/SQL?

    The first day of the year query, along with the last day of the year query, are really not needed, unless the Gregorian calendar is replaced with the Vulcan calender, the Romulan calendar or some other calendrical device. Also the very last query won't work outside of PL/SQL as DATE and DATETIME types are not assignable through the SQL*Plus variable statement. Even rewriting it to replace :d1 with '&&' returns this error in 11.2.0.3:

    1 SELECT ADD_MONTHS(trunc('&&1','MONTH'),i) strt_date
    2 ,LAST_DAY(ADD_MONTHS('&&1',i)) end_date
    3 FROM XMLTABLE('for $i in 0 to xs:int(D) return $i' passing xmlelement(d, floor(months_between(ADD_MONTHS(trunc('&&1','YEAR')-1,12),'&&1')) )
    4* columns i integer path '.')
    SQL> /
    Enter value for 1: 01-JAN-13
    old 1: SELECT ADD_MONTHS(trunc('&&1','MONTH'),i) strt_date
    new 1: SELECT ADD_MONTHS(trunc('01-JAN-13','MONTH'),i) strt_date
    old 2: ,LAST_DAY(ADD_MONTHS('&&1',i)) end_date
    new 2: ,LAST_DAY(ADD_MONTHS('01-JAN-13',i)) end_date
    old 3: FROM XMLTABLE('for $i in 0 to xs:int(D) return $i' passing xmlelement(d, floor(months_between(ADD_MONTHS(trunc('&&1','YEAR')-1,12),'&&1')) )
    new 3: FROM XMLTABLE('for $i in 0 to xs:int(D) return $i' passing xmlelement(d, floor(months_between(ADD_MONTHS(trunc('01-JAN-13','YEAR')-1,12),'01
    JAN-13')) )
    FROM XMLTABLE('for $i in 0 to xs:int(D) return $i' passing xmlelement(d, floor(months_between(ADD_MONTHS(trunc('01-JAN-13','YEAR')-1,12),'01-JAN-13')
    )
    *
    ERROR at line 3:
    ORA-00932: inconsistent datatypes: expected DATE got NUMBER

    SQL>

    Such queries, when offered, should be tested to ensure no such problems arise. There are those who frequent this forum who take such code 'as-is' and when it doesn't work blame themselves, wihch is not fair to them, in my opinion.
     
    kiran.marla and sambuduk like this.
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hai David,

    I have tested these queries in TOAD tool and those working fine. I didn't tested in SQL* Plus.

    In that last query instead of '&&1' if we use to_date('&&1','DD-MON-YYYY') then it will work.

    ---
    Sambasiva Reddy.K
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not everyone uses TOAD; it's best to test these in SQL*Plus as well to determine if they do work and to make necessary changes to the code if they don't. I'll add that the 'first day of the month' query is also pointless since every month that I know of (in the currently accepted Gregorian calendar) starts with the first.

    Unless you can prove otherwise. :)