Home » SQL & PL/SQL » SQL & PL/SQL » IBM DB2 to Oracle PLSQL Current Day and day code conversion
IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638523] Fri, 12 June 2015 21:15 Go to next message
pumelayurika
Messages: 7
Registered: June 2015
Location: USA
Junior Member

Please help me convert a function from IBM DB2 to ORA PLSQL

DB2 code:

((current date) - (day (current date)) day)


I tried using default timestamps functions in PLSQL but it won't work as what I desired.
I always encounter the missing expression error.
But my code works when I am commenting out the function above.
Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638524 is a reply to message #638523] Fri, 12 June 2015 23:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You should tell us what the expression does otherwise most of us, including me, will just don't understand it.

Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638526 is a reply to message #638523] Sat, 13 June 2015 00:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi,

Welcome to the forum. Please provide a test case.
Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638527 is a reply to message #638524] Sat, 13 June 2015 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also tell us what is the datatype and/or format of the result.

Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638539 is a reply to message #638523] Sun, 14 June 2015 06:01 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Looks to me like you're trying to derive either the first day of the current month or possibly the last day of the previous month. Not familiar with DB2, so not 100% sure.
Either way, using the TRUNC function will be your best start point.
The link supplied contains lots of useful examples for date calculations.
Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638541 is a reply to message #638539] Sun, 14 June 2015 22:07 Go to previous messageGo to next message
pumelayurika
Messages: 7
Registered: June 2015
Location: USA
Junior Member

"get last date of prev. month" is the right answer,sorry for Necro-replying, im sorry for late response, I'm on vacation, you can add me in fb.com/phumfang for more info. thanks guys!

[Updated on: Sun, 14 June 2015 22:19]

Report message to a moderator

Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638542 is a reply to message #638541] Sun, 14 June 2015 22:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>"getting last date of previous month" is the right answer,
The first date of the present month is ALWAYS DD=01, so just subtract 1 day from the first of present month to obtain last day of previous month.

SQL> SELECT To_char(To_date('01/' 
                       ||Substr(To_char(SYSDATE, 'MM/YYYY'), 1, 7), 'DD/MM/YYYY' 
               ) - 1, 
              'YYYY-MM-DD') LAST_DAY 
FROM   dual   2    3    4    5  
  6  ;

LAST_DAY
----------
2015-05-31

Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638543 is a reply to message #638542] Sun, 14 June 2015 22:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Or, use LAST_DAY and ADD_MONTHS functions.

LAST_DAY(ADD_MONTHS(SYSDATE,-1))
Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638544 is a reply to message #638542] Sun, 14 June 2015 22:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@BS,

The substr part to get the first day of current month could be simplified to:

TRUNC(SYSDATE, 'MM')
icon10.gif  Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638545 is a reply to message #638544] Sun, 14 June 2015 23:42 Go to previous messageGo to next message
pumelayurika
Messages: 7
Registered: June 2015
Location: USA
Junior Member

Lalit Kumar B wrote on Sun, 14 June 2015 22:37
@BS,

The substr part to get the first day of current month could be simplified to:

TRUNC(SYSDATE, 'MM')

Hi Lalit, thanks -pum
Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638546 is a reply to message #638545] Sun, 14 June 2015 23:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sorry I didn't have access to DB earlier, you could do it like this:

SQL> SELECT SYSDATE, TRUNC(SYSDATE, 'MM') -1 LAST_DAY_PREV_MNTH FROM dual;

SYSDATE   LAST_DAY_
--------- ---------
15-JUN-15 31-MAY-15

SQL> SELECT SYSDATE, LAST_DAY(ADD_MONTHS(SYSDATE,-1)) LAST_DAY_PREV_MNTH FROM dual;

SYSDATE   LAST_DAY_
--------- ---------
15-JUN-15 31-MAY-15

SQL>


To display it in your desired format, use TO_CHAR along with your desired format model. You could see Blackswan's example how TO_CHAR is used.

[Updated on: Sun, 14 June 2015 23:53]

Report message to a moderator

Re: IBM DB2 to Oracle PLSQL Current Day and day code conversion [message #638547 is a reply to message #638545] Mon, 15 June 2015 00:06 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't forget that Oracle dates also contains time part:
SQL> select sysdate "NOW", trunc(sysdate,'MONTH')-1 "PREV_LAST_DAY" from dual;
NOW                 PREV_LAST_DAY
------------------- -------------------
15/06/2015 07:05:46 31/05/2015 00:00:00

Previous Topic: merge columns from 2 different tables
Next Topic: Interview Question
Goto Forum:
  


Current Time: Fri Apr 19 19:17:20 CDT 2024