1

Objective

I have a column with DATE types in the format DD-Mon-YY (i.e. 25-JUN-16), which is an extended date format. I want to use this column as a criteria for a query for entries within the past 10 days and apply logic to it.

Tools: C#, Oracle, LINQ to Entities.


Code

DateTime startDate = DateTime.Today.AddDays(-10);
DateTime endDate = DateTime.Today;

var dates = (from x in db.MY_TABLE
            where x.TIMESTAMP >= startDate && 
            x.TIMESTAMP <= endDate 
            select x);

Problem

When I run this code, nothing populates my list. I have read other threads about this problem, but all solutions I've come across suggest converting the Date to a string first and then to a DateTime. I cannot do this because I need to use the Date to query first and LINQ to Entities does not have a ParseExact method.

Community
  • 1
  • 1
ThanksInAdvance
  • 497
  • 2
  • 7
  • 13
  • What is `db.MY_TABLE` exactly? Your `from .. select ..` part really returns some value? – Soner Gönül Jun 25 '16 at 17:17
  • @SonerGönül I'm using EF, so I set "Entities db = new Entities()" and MY_TABLE is a table within db. I checked my query directly in SQL and values do appear, so I know there are valid values. – ThanksInAdvance Jun 25 '16 at 17:20
  • Did you try the `DbFunctions.TruncateTime` as suggested in your [previous question](http://stackoverflow.com/questions/37991696/compare-dd-mm-yyyy-datetime)? – Ivan Stoev Jun 25 '16 at 17:20
  • @Ivan Stoev yes but the issue wasn't the time. I believe the issue deals with comparing a Date and DateTime type. – ThanksInAdvance Jun 25 '16 at 17:21
  • Sorry. Actually the above should work in EF w/o problem. And works in EF6.1.3, SqlServer db. Unfortunately I can't test in Oracle. – Ivan Stoev Jun 25 '16 at 17:33
  • 1
    I can only comment from an Oracle perspective but Oracle does not have a `DateTime` type - it has `Date` and `Timestamp` and **both** have a time component. Also, in Oracle Dates do not have a format - they are [stored internally as 7- or 8-bytes](http://stackoverflow.com/a/13568348/1509264) – MT0 Jun 25 '16 at 17:33
  • DateTime.Today returns `25-JUN-16 00:00:00` your condition is `timestamp between 15-JUN-16 00:00:00 and 25-JUN-16 00:00:00`... so most of `25-JUN-16` timestampts are outside the range... for exampe `25-JUN-16 12:01:01` is outside the range. can this be a reason? – are Jun 26 '16 at 08:55

0 Answers0