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!

Get period from date

Discussion in 'SQL PL/SQL' started by Milan6687, Jun 9, 2016.

  1. Milan6687

    Milan6687 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    Hi All,

    I need sql query to get period from date column. Below are the conditions:
    1> If 1st day of the year i.e 1st jan is Saturday then 1st period start from that day else from the last saturday.
    2>Each period is having 28 days.After 28 days next period start and it continues.
    For e.g if the date is 2nd feb 2016 then period should be 2.

    Thanks,
    Milan
     
    Last edited: Jun 9, 2016
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Provide here an example of your data .

    for example :
    Code (SQL):
    SELECT
        add_months(trunc(sysdate,'yyyy'),level-1) d1,
        last_day(add_months(trunc(sysdate,'yyyy'),level-1)) d2  
    FROM dual
    CONNECT BY level <= months_between(sysdate,trunc(sysdate,'yyyy'))+1;

    SQL>
    D1          D2
    ----------- -----------
    01.01.2016  31.01.2016
    01.02.2016  29.02.2016
    01.03.2016  31.03.2016
    01.04.2016  30.04.2016
    01.05.2016  31.05.2016
    01.06.2016  30.06.2016
     
    Last edited: Jun 9, 2016
  3. Milan6687

    Milan6687 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Bangalore
    Hi,
    This is for finding 1st and last day of the month. My requirement is getting period from my date column. For e.g if i have a date like 9-june-2016 what should be the period. As i mentioned earlier my first period starts from 1st jan if that day is saturady else from previous saturday and each period will contain 28 days.
    Thanks,
    Milan
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    proceeding from the description of your task, perhaps, you need here such query....
    Code (SQL):
    SELECT
        z.sd+(level-1)*28 + decode(level,1,0,level)sd,
        z.sd+(level-1)*28 +28+decode(level,1,0,level) ed
    FROM ( SELECT trunc(sysdate,'yyyy') - decode(to_char(trunc(sysdate,'yyyy'),'DY'),'SAT',7,0) sd FROM dual) z
    CONNECT BY level <=  (trunc(sysdate)- z.sd)/28 + 1;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Taking this a step further you can return the start date, end date and period with this:

    Code (SQL):
    SELECT sd, ed, period
    FROM
    (SELECT sd, ed, ROW_NUMBER() OVER (ORDER BY sd) period
    FROM
    (SELECT
        z.sd+(level-1)*28 + decode(level,1,0,level)sd,
        z.sd+(level-1)*28 +28+decode(level,1,0,level) ed
    FROM ( SELECT trunc(sysdate,'yyyy') - decode(to_char(trunc(sysdate,'yyyy'),'DY'),'SAT',7,0) sd FROM dual) z
    CONNECT BY level <=  (trunc(sysdate)- z.sd)/28 + 1))
    WHERE &dt BETWEEN sd AND ed;