6

Hi I am trying to write linq query to get some details from Sql table. I have created column and storing date and time both. while returning i want to ommit time part. May I know is this possible?

  List<returnObject> obj = new List<returnObject>();
obj = (from c in objectDB.NCT_Project
  join user in objectDB.NCT_UserRegistration on c.adminUserId equals user.User_Id
  where c.adminUserId == userId
                       select new returnObject
                       {
                           id = c.project_Id,
                           key = c.key,
                           created = c.createdDate //currently returns datetime
                       }).ToList();

Any help would be appreciated. Thank you.

Niranjan Godbole
  • 2,135
  • 7
  • 43
  • 90
  • If you want it to stay a `DateTime` object you can't. If you just want to display it and only want the date do `.ToString(dd-MM-yyyy)` (enter date format - https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx) – EpicKip May 11 '17 at 10:07
  • Protip: don't touch the `DateTime` object. If you're only concerned with the date values, then only use the date properties of the object. You'll run into trouble later on if you start chopping off data unnecessarily. – Abion47 May 11 '17 at 10:09
  • could you post what's on your `returnObject`? – Jeric Cruz May 11 '17 at 10:11
  • Thanks. my return object contains public Nullable created { get; set; } – Niranjan Godbole May 11 '17 at 10:12
  • if you want to have a date only value in your `created` property, you can just have `.ToString("MM/dd/yyyy"); See this link for reference. https://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx – Jeric Cruz May 11 '17 at 10:16
  • There does not appear to be any need to omit the time portion within your application layer, so why isn't this being done at the UI via ToString? – Mad Myche May 11 '17 at 11:12

4 Answers4

17

Use DbFunctions.TruncateTime method:

created = DbFunctions.TruncateTime(c.createdDate)

According to the docs:

When used as part of a LINQ to Entities query, this method invokes the canonical TruncateTime EDM function to return the given date with the time portion cleared.

General Grievance
  • 4,555
  • 31
  • 31
  • 45
YuvShap
  • 3,825
  • 2
  • 10
  • 24
2

All you need to do is call 'Date' property on createdDate.

select new returnObject
                       {
                           id = c.project_Id,
                           key = c.key,
                           created = c.createdDate.Date
                       }).ToList();
Divisadero
  • 895
  • 5
  • 18
1

you can try this one. 

 created = c.createdDate.ToString("HH:mm")
 created = c.createdDate.ToString("H:mm") 
 created = c.createdDate.ToString("hh:mm tt") 
 created = c.createdDate.ToString("h:mm tt")

also see this question : How to get only time from date-time C#

Community
  • 1
  • 1
Drew Aguirre
  • 375
  • 2
  • 15
  • problem with using ToString is that you have to reparse it as soon as you need another format or sorting ability... you should keep DateTime or TimeSpan instance as long as possible and 'ToString' it in UI layer... – Divisadero May 11 '17 at 10:22
  • Yeah you're right.. not the best approach I think.. I will try to find a better solution... hmmmm Thanks @Divisadero – Drew Aguirre May 11 '17 at 10:24
  • there is a TimeOfDay property on the DateTime that is TimeSpan type. Seems enough. But regarding reading from database, DbFunctions seems as the best practice. – Divisadero May 11 '17 at 10:25
  • Yup you're right.. let's vote the answer from @SomeUser . base from the docs. https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions.truncatetime(v=vs.113).aspx – Drew Aguirre May 11 '17 at 10:30
-1

If you can get date comparison out of the LINQ and leave the rest there, you can use this syntax:

sqlite.Query<Entity>("date comparison").Where("other queries")

The predicate I used in the Query() function had to return only todays orders and looked something like this:

select * from Order where date(orderDate/ 10000000 - 62135596800, 'unixepoch') = date('now')
MelOS
  • 595
  • 5
  • 10