Solved

Comparing dates in Oracle

Posted on 2013-06-15
3
622 Views
Last Modified: 2013-06-16
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?
0
Comment
Question by:morinia
3 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39250636
trunc(sysdate,'MM') -1    -- start of the current month MINUS  1 DAY
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 39250696
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39250800
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now