Home » SQL & PL/SQL » SQL & PL/SQL » Urgent SQL Assistance (Oracle 10g SunOS)
Urgent SQL Assistance [message #285836] Wed, 05 December 2007 19:07 Go to next message
Milton C. Craighead, Jr.
Messages: 3
Registered: July 2003
Junior Member
I've created a sql statement that I'm try get the number of messages that will expire during a certain time period. I'm looking to purge messages according to mm/dd/yyyy. I'm having a world of difficulty figuring the statement format out assistance would be appreciated. Oracle 10g

SQL script:

select originationtime, expirationtime, ((TO_DATE('01/01/1970', 'MM/DD/YYYY')) + (originationtime/(86400000.0)) - (5.0/24.0)), ((TO_DATE('01/01/1970', 'MM/DD/YYYY')) + (expirationtime/(86400000.0)) - (5.0/24.0)) from messages

gives me the following:

originationtime expirationtime
dd/mm/yyyy hh:mm dd/mm/yyyy hh:mm

this will go on for 600,000 rows with different dates and times I need to obtain a count of the number of messages that with a expirationtime of say 06/29/2011 then do the same for messages with an expirationtime for 09/15/2007. Thanks...
Re: Urgent SQL Assistance [message #285837 is a reply to message #285836] Wed, 05 December 2007 20:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.jlcomp.demon.co.uk/faq/rowcount.html

If/when you use URGENT, you are just as likely to be ignored.

Please read & FOLLOW posting guidelines as stated in URL below:
http://www.orafaq.com/forum/t/88153/0/
Re: Urgent SQL Assistance [message #285857 is a reply to message #285836] Wed, 05 December 2007 22:22 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Dont assume that members for forum are very much familiar to your system .

You need to be specific on

1/ what data message table contains ?
2/ What is the data type of fields ?
3/ why are youy using quey like ?
   ((TO_DATE('01/01/1970', 'MM/DD/YYYY')) + (originationtime/(86400000.0)) - (5.0/24.0))

4/ What are you really trying to look for ?
5/ What did you try so far ?
6/ what is the O/p of your query ?
7/ what is wrong with output ?
8/ what is your expected result ?

and provide the create and insert script for the sample data

Thumbs Up
Rajuvan.
Previous Topic: Initialization of a bind variable
Next Topic: Duplicate Address
Goto Forum:
  


Current Time: Sat Dec 10 11:10:41 CST 2016

Total time taken to generate the page: 0.04428 seconds