?
Solved

Specify "yesterday" in Oracle date syntax - EASY POINTS!

Posted on 2014-09-04
8
Medium Priority
?
2,245 Views
Last Modified: 2014-09-04
Greetings, esteemed experts!

Having worked with DB2 for the past couple decades, I'm now having to write SQL code for Oracle. I have a need to return "yesterday's date" in a character-format.

It works great when returning today's date in character format, and it works great returning yesterday's date in date-format, but it seems to fail when returning yesterday's date in character-format.

e.g.
select current_date,
       to_char(current_date)
  from dual
;


CURRENT_DATE TO_CHAR(CURRENT_DATE)
------------ ---------------------
04-SEP-14    04-SEP-14            

select current_date - 1 day
  from dual
;

DAY      
---------
03-SEP-14

select to_char(current_date - 1 day)
  from dual
;

ORA-00907 : missing right parenthesis

Open in new window


I realize this is probably mind-numbingly easy for most of you, but I'm stumped. What am I doing wrong?

Thanks!
DaveSlash
0
Comment
Question by:Dave Ford
[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
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40304227
provide a format mask for display it in any format you want:
to_char(current_date-1,'MM/DD/YYYY HH24:MI:SS')
0
 
LVL 1

Expert Comment

by:Radha M
ID: 40304228
Try this.
select to_char(to_date('12-sep-06','dd-mon-yy')) from dual;

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40304233
>>Try this.

to_char without an explicit format mask will return just what the asker posted.

If defaults to DD-MON-YY, then controlled by the parameter NLS_DATE_FORMAT.

It is always best to explicitly set the format mask you want and not leave it up to defaults.
0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40304265
The problem is your date math

simply remove the units,  default date math is in terms of DAYS in Oracle

select sysdate, sysdate - 1 from dual;


when you did this...

select current_date - 1 day   from dual

that was doing the correct math, but the "day" portion was actually just an alias for the calculation results like

select current_date - 1  see_its_just_an_alias  from dual
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40304271
if you want to specify units, you can use interval math

I have no idea why the number must be passed in as a string, but that's the rule

select sysdate, sysdate - interval '1' hour from dual
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40304273
you can also use functions to construct an interval, not the units are a string here, as opposed to the literal in the previous example


select sysdate, sysdate - numtodsinterval(1,'hour') from dual
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40304289
So, pulling all together...

select to_char(current_date - 1)   from dual;

select to_char(current_date - interval '1' day)) from dual;

select to_char(current_date - numtodsinterval(1,'day'))   from dual;

you can include formats as noted above but they have nothing to do with the actual error you saw


You may note I used sysdate previously but current_date here.  I prefer sysdate - one, it's few keystrokes :)
but more importantly, sysdate is the system time.

current_date is the system time adjusted for your session's time zone  meaning two users one in CA and one in NY will get different results for current_date but not for sysdate.

of course, in some usage, the localization would be preferred, but in most cases I've seen, it's not.
0
 
LVL 18

Author Closing Comment

by:Dave Ford
ID: 40304438
Sdstuber, you rock! Thanks for your help!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

762 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