Solved

Comparing dates in Oracle

Posted on 2013-06-15
3
666 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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS Combine projects into one 2 34
Filemaker question - Daily Task 5 47
EF5: Update Model from Database not working 3 79
Using a combo box to search a form. 3 35
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

732 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