Results 1 to 6 of 6

Thread: Date

  1. #1
    Junior Member
    Join Date
    Oct 2008
    Answers
    1

    Date

    Hi,


    select * from table
    where CL_DATE_TIME >='start_date'
    and CL_DATE_TIME<'end_date'

    Here the start_date has to be the first date of previous month and
    end_date has to be the first date of current month.


    e.g start_date='01-Jan-2010'
    end_date='01-feb-2010'

    How can I get the required dates in a variable, every month, as I want to automate this script.

    Thanks in advance!!!!


  2. #2
    Contributing Member
    Join Date
    Jan 2010
    Answers
    35

    Re: Date

    (1.) First of all, register your custom function; as following

    CREATE OR REPLACE FUNCTION first_day_of_month(value_in DATE)

    RETURN DATE IS

    vMo VARCHAR2(2);

    vYr VARCHAR2(4);

    BEGIN

    vMo := TO_CHAR(value_in, 'MM');

    vYr := TO_CHAR(value_in, 'YYYY');

    RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');

    EXCEPTION

    WHEN OTHERS THEN

    RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');

    END fday_ofmonth;

    /


    (2.) To get the First day of the current month, use following Sql

    SELECT first_day_of_month(sysdate) FROM dual

    (3.) To get the First day of the Last month, use following Sql

    SELECT first_day_of_month(fday_ofmonth(sysdate)-1) FROM dual



  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Date

    u just pass date varibale in sql query ....

    select * from table
    where CL_DATE_TIME >=LAST_DAY(TO_DATE('15-jan-2010'))-TO_CHAR(LAST_DAY(TO_DATE('15-jan-2010'))-1,'DD')
    and CL_DATE_TIME< LAST_DAY(TO_DATE('15-jan-2010'))+1

    Sireesha

  4. #4

    Re: Date

    try this
    select * from table
    where CL_DATE_TIME >=add_months(trunc(sysdate,'Month'),-1)
    and CL_DATE_TIME

  5. #5
    Junior Member
    Join Date
    Aug 2010
    Answers
    2

    Re: Date

    Try this one ...

    select * from table
    where CL_DATE_TIME BETWEEN add_months(trunc(sysdate,'Month'),-1)
    and TRUNC(SYSDATE,'MM')


  6. #6
    Junior Member
    Join Date
    Jun 2009
    Answers
    18

    Re: Date

    create or replace procedure p_givedate(dt date) is
    en_dt date;
    st_dt date;
    begin

    select * from where st_dt>=trunc(add_months(dt,-1),'month')and en_dt<=trunc(dt,'month') ;
    end;


  7.  Sponsored Ads
    Ad


     

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact