Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

select tomorrows date

SOLVED
Go to solution
Ratzie
Super Advisor

select tomorrows date

How would I run a select on anything tomorrow and beyond.
I know I should pull from dual but I am blanking on the format.
6 REPLIES
A. Clay Stephenson
Acclaimed Contributor
Solution

Re: select tomorrows date

The key to what you are trying to do is "SELECT SYSDATE + 1 FROM dual;"
If it ain't broke, I can fix that.
Indira Aramandla
Honored Contributor

Re: select tomorrows date

Hi LHradowy,

To system date you query sysdate from dual and the default format is 'DD-MON-YYYY'

Sql> select sysdate from dual;
SQL> select sysdate from dual;

SYSDATE
-----------
04-MAY-2006

As Stephenson mentioned for tomorrows date you do sysdate +1

SQL> select sysdate +1 from dual;

SYSDATE+1
------------
05-MAY-2006

To get the date in your desired format (eg:- DD MON YYYY HH:MI:SS)

SQL> select to_char(sysdate,'DD MON YYYY HH:MI:SS') from dual;

TO_CHAR(SYSDATE)
--------------------
04 MAY 2006 08:43:29

To get the date in your desired format (eg:- DD/MM/YY HH:MI:SS)

SQL> select to_char(sysdate,'DD/MM/YY HH:MI:SS') from dual;

TO_CHAR(SYSDATE)
-----------------
04/05/06 08:44:41


Indira A
Never give up, Keep Trying
Peter Godron
Honored Contributor

Re: select tomorrows date

Hi,
can you please clarify your question.

The way I read it you want to select records from an Oracle table where a date column is in the future?

select empno,ename from emp where hire_date > sysdate + 1;
Please remember this includes the time !

Please let us know if this is not correct.

Arturo Galbiati
Esteemed Contributor

Re: select tomorrows date

Hi,
this will return the tomorrow date
select to_char(sysdate+1,'DD MON YYYY HH:MI:SS') from dual;

HTH,
Art
Hein van den Heuvel
Honored Contributor

Re: select tomorrows date

"Tomorrow and beyond" ... does that mean "after midnite tonite"

In that case Peter is probably on to something:

>> select empno,ename from emp where hire_date > sysdate + 1;
>> Please remember this includes the time !

You may have to get rid of the minutes.

I dont do SQL programming much, so I may not have the easiest method, but one brute-force method could be to take the date+time, select to just date char and reconvert to date+time stamp.

example:

SQL> select to_char( to_date( to_char(sysdate+1,'yyyymmdd'),
'yyyymmdd'),
'DD MON YYYY HH24:MI:SS') "Tomorrow" from dual;

Tomorrow
--------------------
05 MAY 2006 00:00:00

To use ina select against a date-time field in a table that final to_char can be dropped.
This could look like (untested) :

select empno,ename from emp where
hire_date > to_date(to_char(sysdate+1,'yyyymmdd'),'yyyymmdd');

Check 'the SQL cookbook' ?

hth,
Hein.






Hein van den Heuvel
Honored Contributor

Re: select tomorrows date



I _knew_ that date+time --> ascii date alone --> date with 0 time was silly.

Oracle has a build-in functions for this:

SQL Ref...

"TheTRUNC (date) function returnsdate with the time portion of the day truncated
to the unit specified by the format modelfmt. If you omitfmt, thendate is
truncated to the nearest day."

It even has arguments, like for trunctatign to the first day in the year, or first day in the week:

SQL> select to_char(trunc(sysdate+1),'DD MON YYYY HH24:MI:SS') "Tomorrow" from dual;

Tomorrow
--------------------
06 MAY 2006 00:00:00

SQL> select to_char(trunc(sysdate+1,'y'),'DD MON YYYY HH24:MI:SS') "First of the year" from dual;

First of the year
--------------------
01 JAN 2006 00:00:00

SQL> select to_char(trunc(sysdate+1,'w'),'DD MON YYYY HH24:MI:SS') "First of the week" from dual;

First of the week
--------------------
01 MAY 2006 00:00:00

fwiw,
Hein.