?
Solved

Select records based on Timestamp

Posted on 2006-04-14
4
Medium Priority
?
1,719 Views
Last Modified: 2008-01-09
Hi

This is rather simple question, i might doing something wrong. I have a table with one of the column as timestamp.
The data stored is in format MM/DD/YYYY 24HH:MI:SS.
I want to select the records greater or equal to a certain date.
When i try select * from <tablename> where to_char(timestamp,'MM\DD\YYYY') >= '04/12/2006 5:41:43 PM'
it is returning all the records even before the date given in where condition.

Thanks
0
Comment
Question by:KOTTUSRI
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
ram_0218 earned 2000 total points
ID: 16455742
no. For doing the date or timestamp comparison you got to use to_date, if you use to_char, its gonna do a alphabetic sort not a date sort thus you wont get proper records as you expect. Try this.

select * from <tablename> where to_date(timestamp,'MM\DD\YYYY') >= to_date('04/12/2006 17:41:43','mm/dd/yyyy hh24:mi:ss')
/

0
 

Author Comment

by:KOTTUSRI
ID: 16455887
Its says "ORA-01843: not a valid month"
0
 
LVL 17

Expert Comment

by:ram_0218
ID: 16456049
Try now.

if timestamp is a date or timestamp field, then directly do this.
select * from <tablename> where timestamp >= to_date('04/12/2006 17:41:43','mm/dd/yyyy hh24:mi:ss')

if timestamp  is a varchar2 field, do this.
select * from <tablename> where to_date(to_char(timestamp,'mm/dd/yyyy hh24:mi:ss'),'mm/dd/yyyy hh24:mi:ss') >= to_date('04/12/2006 17:41:43','mm/dd/yyyy hh24:mi:ss')

0
 

Author Comment

by:KOTTUSRI
ID: 16456787
I got the output when i do select * from <tablename> where timestamp >= to_date('04/12/2006 17:41:43','mm/dd/yyyy hh24:mi:ss')

Thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question