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

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
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
provide a format mask for display it in any format you want:
to_char(current_date-1,'MM/DD/YYYY HH24:MI:SS')
0
Radha MCommented:
Try this.
select to_char(to_date('12-sep-06','dd-mon-yy')) from dual;

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
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
sdstuberCommented:
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
sdstuberCommented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
Sdstuber, you rock! Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.