Comparing dates in Oracle

I am having a problem with the syntax when trying to compare dates when the table is residing in Oracle.

I would like to compare DATEA =  12/31/2011.

However, if possible I would like the actual compare to be:
DATEA = the last day of the prior month of the date I am inputting.

In sql server I can use
DATEA = DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,{ts '2012-01-01 00:00:00'}),0)))

Is there a comparable syntax that will work in Oracle?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
flow01Connect With a Mentor Commented:
trunc(sysdate,'MM') -1    -- start of the current month MINUS  1 DAY
0
 
ajexpertCommented:
Check this

 
DECLARE
   v_date   DATE := TO_DATE ('12/31/2011', 'MM/DD/YYYY');
BEGIN
   v_date := LAST_DAY (ADD_MONTHS (v_date, -1));

   DBMS_OUTPUT.PUT_LINE (v_date);
END;

Open in new window

0
 
PortletPaulCommented:
Oracle:
SELECT
TO_TIMESTAMP('2012-01-01','yyyy-mm-dd') - INTERVAL '0.001' SECOND FROM dual;
or
SELECT  date '2012-01-01' - INTERVAL '0.001' SECOND FROM dual;

But let's look more closely at your TSQL...
select 
   DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,{ts '2012-01-01 00:00:00'}),0)))

-- all that could be achieved more simply by:
select dateadd(ms,-3,'2012-01-01')

Open in new window

All the above is an attempt to get to a very late point of the day in 2011-12-31

It would be relatively rare that DATEA would in fact EQUAL such a value, and this type of logic appears to me as if you are attempting to locate a date range like:

--tsql
select
*
from sometable
where datea between '2011-12-01'
and  dateadd(ms,-3,'2012-01-01') -- by deducting some arbitrary amount

a far more reliable method is simply to avoid "between" and use LESS THAN on the upper boundary

-- tsql
select
*
from sometable
where datea >= '2011-12-01'
and  datea < '2012-01-01' -- i.e. everything in Dec 2011

so, the quivalent Oracle syntax would be:

--Oracle
select
*
from sometable
where datea >= to_date('2011-12-01' ,yyyy-mm-dd')
and  datea < to_date('2012-01-01','yyyy-mm-dd') -- i.e. everything in Dec 2011

or, an alternative syntax in Oracle 11g, nb: requires yyyy-mm-dd (must use dash, no other seperator)

-- Oracle 11g
select
*
from sometable
where datea >=  date '2011-12-01'
and  datea <  date '2012-01-01' -- i.e. everything in Dec 2011

Put simply, don't use between and the need for these time unit deductons can be avoided in both SQL Server and Oracle.
For more on this topic please see: "Beware of Between"

also see: Date Functions in Oracle and Microsoft SQL Server
0
All Courses

From novice to tech pro — start learning today.