Operating System - HP-UX
1748348 Members
5176 Online
108762 Solutions
New Discussion юеВ

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 5
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.