Select oracle date column using C#

Question

I am trying to select data from my Oracle database BY DATE using C#. However I am always getting an empty data set although the same query string works just fine in Oracle SQL Developer

String Query = "Select position_date from position";
OracleDataAdapter adapter = new OracleDataAdapter(Query, ocon); 
adapter.Fill(ds, "table"); //where ds is a dataset 
PrintDataSet(ds);

returns

3/8/2011 12:00:00 AM.... and more

However, when I change my query to below, then there is no output!

String Query = "Select position_date from position 
where to_char(position_date, 'mm-dd-yyyy') = '05-17-2012'"

This query works fine in oracle sql developer. I've also tried trunc(sysdate) but nothing seems to work! :(

Bit rusty, but try this - "select * from position where position_date = to_date('05-17-2012', 'mm-dd-yyyy')" — glennanthonyb
– glennanthonyb, Commented May 17, 2012 at 22:19
Well 3/8/2011 is a completely different day than 05-17-2012. It does make sense to me that you are not getting anything back. And you should not convert the date to a string but the string to a date. Using position_date = to_date('05-17-2012', 'mm-dd-yyyy') will enable Oracle to use an index on position_date. — user330315
– user330315, Commented May 17, 2012 at 22:19
@horse, That may be so, but it is not an equivalent query. The posted query should get everything on 5/17/12, your query gets you everything on 5/17/12 at midnight. You probably meant TRUNC(position_date) = to_date('05-17-2012', 'mm-dd-yyyy') — DCookie
– DCookie, Commented May 17, 2012 at 22:27
What does 'query works fine in oracle sql developer' mean? Is it returning any data? — Grzegorz W
– Grzegorz W, Commented May 17, 2012 at 22:29

cs_newbie · Accepted Answer · 2012-05-18 13:11:20Z

1

select * from position where trunc(position_date) = to_date('05-17-2012', 'mm-dd-yyyy')

worked.

Thanks.

answered May 18, 2012 at 13:11

cs_newbie

2,0171 gold badge16 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Aldridge · Accepted Answer · 2012-05-18 08:05:23Z

0

If your dates have no time component (and if that is so then guarantee it with a check constraint), then:

Select position_date
from   position 
where  position_date = date '2012-05-17'

Otherwise:

Select position_date
from   position 
where  position_date >= date '2012-05-17' and 
       position_date <  date '2012-05-17' + 1

answered May 18, 2012 at 8:05

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

1 Comment

cs_newbie Over a year ago

String Query = "Select position_date from position pos where pos.position_date >= date '2012-05-17' and pos.position_date < date '2012-05-17' + 1 and rownum < 11"; This does not return anything when used in C#, but returns 10 rows in Oracle sql developer...

Collectives™ on Stack Overflow

Select oracle date column using C#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related