Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    106

    Unanswered: SQL server syntax into Oracle

    Can someone tell me how to write the following sql code (from sql server) into Oracle syntax:

    select
    convert(datetime,convert(varchar,datepart(month,da teadd(month, -1,getdate())))+'/01/'+convert(varchar,datepart(year,getdate()-1)))as Start_Month,
    convert(datetime,convert(varchar,datepart(month,ge tdate()))+'/01/'+convert(varchar,datepart(year,getdate()-1)))-1 as End_Month

  2. #2
    Join Date
    Oct 2005
    Posts
    5

    Question date addition ?

    what is the equivalent syntax of "getdate()-1" with dateadd function.
    I have an application that translate sql request from t-sql to plsql ...

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    what does the sql in your first post do then we could answer you more easily

    Alan

  4. #4
    Join Date
    Oct 2005
    Posts
    5

    Wink Answer

    This is the result of my automatic translater :
    select to_date(cast(extract ( month from add_months(sysdate ,-1) ) as varchar2(20)) || '/01/' || cast(extract ( year from sysdate -1 ) as varchar2(20)),'MM/DD/YYYY') as Start_Month,
    to_date(cast(extract ( month from sysdate ) as varchar2(20)) || '/01/' || cast(extract ( year from sysdate -1 ) as varchar2(20)),'MM/DD/YYYY') -1 as End_Month
    from dual;

    Think to do it using Oracle functions ( LAST_DAY,...) : it will be more easy to understand.

  5. #5
    Join Date
    Feb 2004
    Posts
    88
    your query returns the first day and the last day of last month.
    so...

    SELECT FIRST_DAY(ADD_MONTHS(SYSDATE, -1) Start_Month,
    LAST_DAY(ADD_MONTHS(SYSDATE, -1) End_Month
    FROM DUAL;

    Its one of those (few) things that are easier in Oracle than in SQL Server!

  6. #6
    Join Date
    Feb 2004
    Posts
    106
    Thank you all for your replies. Wow, it is much easier in oracle. :-)

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The only two drawbacks of Thompbil's solution are the fact that there's no function like "FIRST_DAY" in Oracle (at least not up to 10g release 2). Check list of Oracle datetime (and other SQL funcions) here.

    The other one is that Oracle (unlike SQL Server, which is easier to use) requires two closed parenthesis if two of them are opened:
    Quote Originally Posted by Thompbil
    FIRST_DAY(ADD_MONTHS(SYSDATE, - 1) Start_month
    However, great job, Thompbil! If this query really is to return the firt and the last day of previous month, your query needs just a little tuning: to fetch the first day, you'll have to find the last day in August (regarding it is October now) and add yet another day to find the first day in September. Something like this:
    Code:
    SELECT 
      LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1 Start_Month,
      LAST_DAY(ADD_MONTHS(SYSDATE, -1)) End_Month
    FROM DUAL;

  8. #8
    Join Date
    Feb 2004
    Posts
    106
    Thank you guys. How do I format this so that it only returns the "short date" i.e. 11/07/2005 without the time.

  9. #9
    Join Date
    Jan 2004
    Posts
    492
    trunc(sysdate)

    you just put trunc around the whole last_day (...............) argument
    Oracle OCPI (Certified Practicing Idiot)

  10. #10
    Join Date
    Feb 2004
    Posts
    106
    thank you

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    SELECT trunc(ADD_MONTHS(SYSDATE, -1),'MON') Start_Month,
    TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1))) End_Month
    FROM DUAL;
    Fun thing about Oracle... 20 ways to do the same thing.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •