Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Comparing dates in Oracle

Posted on 2013-06-15
3
Medium Priority
?
680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 2000 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 49

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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