CODEWITHSUNDEEP
sqloracle-database
zak
zak

Reputation: 104

select query with date type in oracle DB

iam writing sql query in oracle database ,but there are table name exist ,but also oracle resualts with error thats the table or view not found

another issue that iam writing this query 

select * from Table_name where DDATE="30-05-2014;

but id doesn't return anything how to write where with date type

Upvotes: 0

Views: 225

Answers (3)

Rock 'em
Rock 'em

Reputation: 187

These might be some of the reasons for getting those error.

1 ) If you get a table or view does not exists error , it means you may not have sufficient privilege on that table.

2) Use a date format like to_date() for date datatypes.

 select * from your_table where to_date(date_column,'DD-MON-YYYY')='30-MAR-2012';

3) Check whether you have data for that particular date. You can use a simple GROUP BY to check for the number of record for that data.

 select count(*),date_column from your_table group by date_column order by date_column desc;

Upvotes: 0

thusith.92
thusith.92

Reputation: 306

as @Alex Poole said.. Use the TO_DATE() function as:

select * from your_table where date_column = TO_DATE('01/01/2014' , 'dd/mm/yyyy');

But make sure the your "date_column" a DATE type column.

Another point to consider is, if the column contains the TIME too (ex: 11/30/2012 10:00:00 AM), Use

select * from your_table where TRUNC(date_column) = TO_DATE('01/01/2014' , 'dd/mm/yyyy');

this will only compare the date fraction and give you the results depending on that.

Cheers!

Upvotes: 3

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

Use a DATE literal instead:

select * from Table_name where VHFDATE= DATE '2014-05-30';

Or, if the date value is being supplied from a UI or from somewhere else, use a bind variable:

select * from Table_name where VHFDATE= :in_vhf_date;

Upvotes: 2

Related Questions