Member_2_2484401
asked on
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.
I realize this is probably mind-numbingly easy for most of you, but I'm stumped. What am I doing wrong?
Thanks!
DaveSlash
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
I realize this is probably mind-numbingly easy for most of you, but I'm stumped. What am I doing wrong?
Thanks!
DaveSlash
Try this.
select to_char(to_date('12-sep-06','dd-mon-yy')) from dual;
>>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.
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.
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
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
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
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
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
select sysdate, sysdate - numtodsinterval(1,'hour') from dual
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sdstuber, you rock! Thanks for your help!
to_char(current_date-1,'MM