Home » SQL & PL/SQL » SQL & PL/SQL » New to SQL Plus
New to SQL Plus [message #184869] Fri, 28 July 2006 07:03 Go to next message
crivelp
Messages: 2
Registered: July 2006
Location: Georgia
Junior Member
I have been using DB2 for several years now. We are re-platforming to Oracle. How would I change the following where clause to be Oracle compliant.

from PS_AR32001_TMP AR32001_TMP
where MONTH(ST_DT) = #curr_month
and YEAR(ST_DT) = #curr_year

Thank You,
Peter Crivello
Re: New to SQL Plus [message #184877 is a reply to message #184869] Fri, 28 July 2006 08:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assumptions:
#current_month and #current_year are system defined date parameters
ST_DT is a column in your table.

A direct syntax translation to Oracle would be :
from PS_AR32001_TMP AR32001_TMP
where to_char(ST_DT,'mm') = to_char(sysdate,'MM')
and   to_char(ST_DT,'yyyy') = to_char(sysdate,'YYYY')


SYSDATE returns the current system date
to_char is used to convert DATE datatypes to VARCHAR2 datatypes (it does other things to)

A more concise syntax would be
from PS_AR32001_TMP AR32001_TMP
where trunc(ST_DT,'mm') = trunc(sysdate,'mm')
icon14.gif  Re: New to SQL Plus [message #184898 is a reply to message #184877] Fri, 28 July 2006 09:25 Go to previous messageGo to next message
crivelp
Messages: 2
Registered: July 2006
Location: Georgia
Junior Member
Thank You! That seems like it will work. I cannot test it yet to next week. This is an SQR that I am retrofitting and the database won't be ready for me to test this until next week or so. How would I modify the following:

from PS_AR32001_TMP AR32001_TMP
where MONTH(ST_DT) = MONTH(current date) - 1
and YEAR(ST_DT) = #curr_year

Once again thank you for the help.
Re: New to SQL Plus [message #184907 is a reply to message #184898] Fri, 28 July 2006 09:56 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's a function called add_months which just adds or subtracts integer months to a date.

SQL> select sysdate,add_months(sysdate,1)
  2  from dual;

SYSDATE              ADD_MONTHS(SYSDATE,1
-------------------- --------------------
28-jul-2006 15:33:48 28-aug-2006 15:33:48


You need to be careful with the last day in a month though - it moves the last day of the month to the last day of the month you end up in.

SQL> select to_date('28-02-2006','dd-mm-yyyy')
  2        ,add_months(to_date('28-02-2006','dd-mm-yyyy'),1)
  3        ,add_months(to_date('28-02-2006','dd-mm-yyyy'),2)
  4  from dual;

TO_DATE('28-02-2006' ADD_MONTHS(TO_DATE(' ADD_MONTHS(TO_DATE('
-------------------- -------------------- --------------------
28-feb-2006 00:00:00 31-mar-2006 00:00:00 30-apr-2006 00:00:00


so I'd do something like:

from PS_AR32001_TMP AR32001_TMP
where trunc(ST_DT,'mm') = add_months(trunc(sysdate,'mm'),-1)




The documentation is pretty good once you know what you're looking for.
Previous Topic: Need Update Query
Next Topic: Procedure help
Goto Forum:
  


Current Time: Sat Dec 10 20:45:57 CST 2016

Total time taken to generate the page: 0.06653 seconds