Solved

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

Posted on 2014-09-04
8
1,493 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:daveslash
8 Comments
 
LVL 76

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 76

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
 
LVL 73

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 73

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 73

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 73

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:daveslash
ID: 40304438
Sdstuber, you rock! Thanks for your help!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now