Simple Query Involving Dates 1

[likelylad]

Hi All

I am completely new to running queries in Oracle so please excuse my stupidity.

I want to show all records from a table from a particular date.

The field is set as a timestamp e.g. 08/06/2006 22:00:48

This is a sample of what I have tried:

Select * from tablename where to_date(fieldname,'dd/mm/yyyy HH:MI:SS')>01/01/2006

Thanking in advance for any help received.

 

[Dagon]

The most important question is: what is the data type of the field that you are querying ? Is it a DATE or VARCHAR2. Describe the table (describe tablename) to find out.

 

[likelylad]

Hi Dagon

Thanks for the quick response.

When I run the Describe statement, it is giving me a ORA-00900 Invalid SQL Statement Error.

If this helps, I have run the following query but it shows all records instead of filtering out the dates

Select * From [i]tablename[/i] Where TO_CHAR(TO_DATE([i]fieldname[/i],'dd/mm/yyyy HH24MISS'))>TO_CHAR('01/11/2006');

 

[likelylad]

Just a bit of additional info

If I run the following

Select * From [i]tablename[/i] Where TO_DATE(TO_CHAR([i]fieldname[/i],'dd/mm/yyyy HH24MISS'))>TO_DATE(TO_CHAR('01/11/2006'));

It gives me the following error

ORA-01843 : Not A Valid Month

 

[Dagon]

I don't know what front-end you are using for your queries. Describe should work from SQL*Plus. You can also get the information by interrogating all_tab_columns:

select * from all_tab_columns
where table_name = 'XXXX'
and columnname = 'FIELDNAME'

It should tell you whether it's a date or varchar2.

 

[likelylad]

Is is showing the datatype as DATE

 

[Dagon]

If it is a DATE, you don't need the TO_DATE function. TO_DATE converts a character string to a date. If it's already a date, that is pointless.

You need a query such as:

Select * From tablename Where datefieldname
> TO_DATE('01/11/2006', 'DD/MM/YYYY');