Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Comparing dates in Oracle

Posted on 2013-06-15
3
Medium Priority
?
683 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 21

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

927 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