select tomorrows date

[Ratzie]

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.

[A. Clay Stephenson · Accepted solution]

The key to what you are trying to do is "SELECT SYSDATE + 1 FROM dual;"

[Indira Aramandla]

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

[Peter Godron]

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]

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]

"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]

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.