OraFAQ Forum: SQL & PL/SQL » Sysdate with time interval parameter

Home » SQL & PL/SQL » SQL & PL/SQL » Sysdate with time interval parameter (Oracle 11i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sysdate with time interval parameter [message #638571]

Mon, 15 June 2015 08:47

RADHAKAN
Messages: 7
Registered: May 2015

Junior Member

HI,

I am new to SQL, can anyone help me in giving the sysdate parameter with time interval.

I have script which i want to run so that it would give the data processed within a time frame on a daily basis.
For example i need report with sysdate -2 06:30:00 PM to sysdate -1 06:30:00 PM.

And can i replace the sysdate with actual date.

Regards,

Report message to a moderator

Re: Sysdate with time interval parameter [message #638572 is a reply to message #638571]

Mon, 15 June 2015 08:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

>For example i need report with sysdate -2 06:30:00 PM to sysdate -1 06:30:00 PM.
what does line above signify?

Report message to a moderator

Re: Sysdate with time interval parameter [message #638573 is a reply to message #638571]

Mon, 15 June 2015 09:03

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Sysdate is an oracle function that returns the current date.
Oracle dates always include a time component.
An interval is the difference between two timestamps (different datatype to date that includes fractional seconds) expressed in days, hours, minutes and seconds.
Sysdate does not come with a time interval.
So really we're not all sure what you're trying to do, and you need to explain in more details.

Report message to a moderator

Re: Sysdate with time interval parameter [message #638577 is a reply to message #638571]

Mon, 15 June 2015 09:09

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

And can i replace the sysdate with actual date.

And what does this mean? Isn't sysdate the actual date?

Report message to a moderator

Re: Sysdate with time interval parameter [message #638581 is a reply to message #638571]

Mon, 15 June 2015 09:22

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Looks to me like determining a certain time of day derived from sysdate, most probably

select trunc( sysdate-1 ) + 18/24 + 30/1440 yesterday_6_30_pm,
  trunc( sysdate-1 ) + interval '18' hour + interval '30' minute another_formula
from dual;

Note the TRUNC function for truncating the SYSDATE (actual date with time) to midnight (day information) and various expressions for adding the required time of day.

Report message to a moderator

Re: Sysdate with time interval parameter [message #638582 is a reply to message #638571]

Mon, 15 June 2015 10:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

RADHAKAN wrote on Mon, 15 June 2015 19:17

For example i need report with sysdate -2 06:30:00 PM to sysdate -1 06:30:00 PM.

As others said, it's confusing. I am just guessing, do you mean you want to use a particular table's column instead of SYSDATE?

Another observation, nothing serious though, you have mentioned your Oracle database version as "11i". Either you are on pre-11g/11g/post-11g, since there is no such version as 11i.

[Updated on: Mon, 15 June 2015 10:24]

Report message to a moderator

Re: Sysdate with time interval parameter [message #638583 is a reply to message #638571]

Mon, 15 June 2015 10:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

RADHAKAN wrote on Mon, 15 June 2015 19:17

For example i need report with sysdate -2 06:30:00 PM to sysdate -1 06:30:00 PM.

Are you trying to keep the time portion fixed as "06:30:00 PM" for respective dates while fetching rows between the given range of dates?

Report message to a moderator

Re: Sysdate with time interval parameter [message #638595 is a reply to message #638573]

Mon, 15 June 2015 12:30

RADHAKAN
Messages: 7
Registered: May 2015

Junior Member

Thanks....

Data are extracted on the basis of the below line

"AND to_date(acra.last_update_date) >= to_date(sysdate) - 2".

It gives me three days data (for e.g. if i run today, it gives me data from 13th June to today. And from there i have to segregate the data based on timing. Which is 13th June 6:3o PM to 14th June 6:30 PM.
So just know if i can filter the above time in the date parameter and schedule it.

Regards,

Report message to a moderator

Re: Sysdate with time interval parameter [message #638596 is a reply to message #638595]

Mon, 15 June 2015 12:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>"AND to_date(acra.last_update_date) >= to_date(sysdate) - 2".
It is silly, nonsensical & wasteful to apply TO_DATE function to any DATE datatype; such as SYSDATE!
TO_DATE is used to convert a STRING into DATE datatype

Report message to a moderator

Re: Sysdate with time interval parameter [message #638597 is a reply to message #638595]

Mon, 15 June 2015 12:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

"to_date(sysdate)" is a silly expression. sysdate IS a date, you can't convert it to a date.
Doing so is one of the gates of hell (with WHEN OTHERS...).

"It gives me three days data " I don't see how subtracting 2 seconds to current datetime can give you 3 days of data.

Report message to a moderator

Re: Sysdate with time interval parameter [message #638598 is a reply to message #638595]

Mon, 15 June 2015 12:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

PLEASE comply with above!

Report message to a moderator

Re: Sysdate with time interval parameter [message #638600 is a reply to message #638598]

Mon, 15 June 2015 13:04

RADHAKAN
Messages: 7
Registered: May 2015

Junior Member

Solved...

Report message to a moderator

Re: Sysdate with time interval parameter [message #638601 is a reply to message #638600]

Mon, 15 June 2015 13:05

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How?

Report message to a moderator

Re: Sysdate with time interval parameter [message #638602 is a reply to message #638600]

Mon, 15 June 2015 13:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

please post your solution as directed by the Posting Guidelines

Report message to a moderator

Re: Sysdate with time interval parameter [message #638603 is a reply to message #638602]

Mon, 15 June 2015 13:46

RADHAKAN
Messages: 7
Registered: May 2015

Junior Member

Using below...

select trunc( sysdate-1 ) + 18/24 + 30/1440 yesterday_6_30_pm,
trunc( sysdate-1 ) + interval '18' hour + interval '30' minute another_formula
from dual;

Report message to a moderator

Re: Sysdate with time interval parameter [message #638619 is a reply to message #638597]

Tue, 16 June 2015 03:08

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Michel Cadot wrote on Mon, 15 June 2015 18:35

"It gives me three days data " I don't see how subtracting 2 seconds to current datetime can give you 3 days of data.

2 seconds?

Report message to a moderator

Re: Sysdate with time interval parameter [message #638621 is a reply to message #638619]

Tue, 16 June 2015 03:22

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

to_date(sysdate) - 2"

Maybe it is not world wide but in my place " is the symbol for second and I didn't see it was a quote. Laughing

Laughing

Report message to a moderator

Previous Topic:	Read a string char by char and compare
Next Topic:	How To Check Images with Zero Bytes in Size on BLOB column datatype

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 19:43:50 CDT 2024