| SQL query with date and time [message #371875] |
Fri, 15 December 2000 16:39  |
rinku
Messages: 4
Registered: December 2000
Location: India
|
Junior Member |
|
|
Hi there,
I am very new to Oracle (basically I am a java programmer) so please answer even if the question is very basic. I have ceated an SQL statement where I have to select data from the MS Access Database on the basis of date and time. Any data before that date and time should be able to be selected. In my Database table I have Fieldname as "Scheduled time" which has values like 12/16/2000 11:35PM. What I want to know is....... that can I select on the basis of both date and time or just by either one of it. If I can select the record by using both date and time at the same time, then how should I do that.
My SQL statement looks like this:
SELECT * FROM database WHERE ScheduledTime < ?
here, " ? " will be replaced by present date and time like " 12/14/2000 11:35PM ".
Database looks like:
name - ScheduledTime - company name
David - 12/16/2000 11:35PM - Microsoft
Michael- 12/24/2000 12:00AM - Oracle
Dana - 11/10/2000 7:30AM - Cisco
Thanks, for the help in advance. Please someone help me as it is very urgent!!
-Rinku
email ID : rrandhawa@compunetix.com
|
|
|
|
| Re: SQL query with date and time [message #371878 is a reply to message #371875] |
Mon, 18 December 2000 03:17  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
A brief synopsis of Oracle Date Handling.
Oracle stores dates and times in a data type called DATE, which will staor a date-time to an accuracy of seconds.
SYSDATE is the current date-time
To convert a string to a date, use the TO_DATE command
eg TO_DATE('12-dec-2001 12:34:00','dd-mon-yyyy hh:mi:ss') will convert the specified string into a date
To do comparisons by date only, use the TRUNC function to remove any time component eg:
WHERE trunc(table_column) < trunc(sysdate)
will get all records where table_column represents a date before today.
It is possible to do a comparison on time only by stripping the time element of a date out into a string, and then adding this to a known day.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
