Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-09-04
8
Medium Priority
?
2,997 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
8 Comments
 
LVL 78

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 78

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

579 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