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.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

sysdate -1 but how about time as well

SOLVED
Go to solution
Ratzie
Super Advisor

sysdate -1 but how about time as well

I know I can do a select on a date field and pull all data, lets say that is > sysdate -1. Or greater then yesterday.

How about day plus time?
I need to pull everything > then yesterday at 6 am.

Going even deep, how about between yesterday at 6 am and today at 6 am?

Oracle 10g database.
5 REPLIES
Indira Aramandla
Honored Contributor
Solution

Re: sysdate -1 but how about time as well

Hi LHradowy,

You can try this.

Lest assume your date column is coldt.

Select coldt where coldt > (sysdate -1) and
to_char(coldt,'HHMISS') > '060000';

This will check the house, minute and second component to be grater than 060000, whcih is after 6 AM.

And for todays records between 00:00 hours and 06:00 hours
do the same with coldt = sysdate and to_char(coldt,'HHMISS') between '000000' and '060100';

I hope this helps.


Indira A




to_char(lastupdatedts,'HHMISS') between '070000' and '102000';
Never give up, Keep Trying
Frank de Vries
Respected Contributor

Re: sysdate -1 but how about time as well

Hi

In fact you can format it any way you want,
see this example

select to_char(sysdate-1,'YYYY-MM-DD:HH24:MI:SS') from dual

or

select to_char(sysdate-1,'HH24:MI:SS MM-DD-YY:') from dual

the formatting is very flexible,
then you can use that in your query
with operators like
greater then > , smaller then <
equals =


Look before you leap
Brian Crabtree
Honored Contributor

Re: sysdate -1 but how about time as well

select * From table where datefield > trunc(sysdate-1+6/24);

That should get you everything from yesterday after 6am system time. Note the 6 in "6/24" can be changed to increase the hours.

(Another quick note, you can use 30/1440 to increase it 30 minutes, etc).

Brian
Hein van den Heuvel
Honored Contributor

Re: sysdate -1 but how about time as well

Brian, small correction... the closing paren should after the date-1 of course.

For grins a test done just before 10pm 5/14:

SQL> select trunc(sysdate-1+6/24) from dual;
14-MAY-06
SQL> select trunc(sysdate-1+2/24) from dual;
13-MAY-06
SQL> select trunc(sysdate-1)+2/24 from dual;
13-MAY-06

SQL> select to_char(trunc(sysdate - 1) + 6/24,'HH24:MI:SS MM-DD-YY') from dual;
06:00:00 05-13-06

Hein.
Brian Crabtree
Honored Contributor

Re: sysdate -1 but how about time as well

Doh, that is correct. Thats what I get for typing something out without proofreading. Thanks for catching that.