Solved

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

Posted on 2014-09-04
8
2,038 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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
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 500 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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

717 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