0

The code below retrieves the DateTime value of the "DateCreated" column in the table:

var result = from t in db.table select t.DateCreated

I need to retrieve the correct Date values but the time part needs to be 0 for all items in the result.

Is there a way to put back the time part with a value of 00:00:00 as in the pseudo code below?

var result = from t in db.table select p.DateCreated.Value.Date + "00:00:00"
Ben Junior
  • 2,449
  • 10
  • 34
  • 51

2 Answers2

1

The Date property of a Datetime type returns the date part only.

So:

var result = from t in db.table select DateCreated = (DateTime)t.DateCreated.Date;
Drunken Code Monkey
  • 1,796
  • 1
  • 14
  • 18
  • 1
    The problem is that when I assign the result back to the model, LinqToSql throws an exception asking for the Time part of it. – Ben Junior Dec 05 '14 at 16:49
  • Just cast it back to a DateTime before. – Drunken Code Monkey Dec 05 '14 at 17:47
  • I don't know how to do it. Have tried few options but all rejected by LinqToSql – Ben Junior Dec 06 '14 at 00:16
  • None of the solutions proposed here will work. LinkToSql will throw an exception. I dont even know how it get a vote. – Ben Junior Dec 07 '14 at 22:12
  • I edited the code above in response to what you posted. The code above will do what you want. First it extracts the Date part of the DateTime (setting its time part to 0), then casts it to a DateTime. – Drunken Code Monkey Dec 08 '14 at 00:05
  • Thanks, I appreciate it, but, as mentioned before, this code will be rejected by LinqToSql. Did you try to run it before posting? For what I know so far, the one that works is the one in the answer wich also has a link to the explanation why. – Ben Junior Dec 12 '14 at 18:32
  • You must be doing something wrong, this code does work fine. You never answered if you are using EF or Linq2SQL. They are different. This code above is what you would use for LinqToSQL. Your answer is what you would use for Entity Framework. So either your question is not worded properly or you are doing something wrong. – Drunken Code Monkey Dec 12 '14 at 18:43
  • OK. Your code works now. I voted it up. My question was not properly formatted. Thanks for the explanation – Ben Junior Jan 15 '15 at 02:49
0

I hate to answer my own question, but I found the solution here

So, the correct LinqToSql sintax to reset the time portion of the date to 00:00:00 is 

var result = from t in db.table select DateCreated = System.Data.Entity.DbFunctions.TruncateTime(t.DateCreated)
Community
  • 1
  • 1
Ben Junior
  • 2,449
  • 10
  • 34
  • 51
  • This not actually the correct answer as it uses an entity framework library. If you are using Linq to SQL as you say, and not entity framework, my answer above is the proper way. – Drunken Code Monkey Dec 08 '14 at 00:08