EntityFunction.TruncateTime not working in my query

Question

I am using Linq to entityframework to query some infomration. I am trying to use entityfunction.truncatetime and it doesnt seem to work as expected. here is my sample query

From d In Request
Where d.Requestor= "XXXX" And d.ProcessedFlag = "N"
Select d.RequestID, RequestReason = d.RequestReason.ItemValue, RequestType =  d.RequestType.ItemValue, RequestedDate = EntityFunctions.TruncateTime(d.RequestedMoveDate)

The requesteddate doesnt seem to truncate the time part and I am still getting the both Date and time.

Am I missing something here?

What do you mean "doesnt seem to truncate the time part and I am still getting the both Date and time"? DateTime type always has the time part. Isn't the time part not set to 00:00:00 ? — Pawel, Oct 15 '12 at 18:51
My goal was to ignore the time part and retreive only the Date part from DB datetime coulumn. So in my understanding the EntityFunctions.TruncateTime would do that. — Aradana, Oct 15 '12 at 19:15
...and then it would load it to a DateTime object which always stores some time since there is not DateWithoutTime type in the .NET Framework — Pawel, Oct 15 '12 at 19:30
okay then do you know how can I get just the date part from the DATETIME DB field using LINQ and EF? But I found the workaround to the issue, but if there is a way to achive this then It will be useful in future. — Aradana, Oct 15 '12 at 19:53
After you truncate like you did (just make sure the time is really 00:00:00 - otherwise it means that truncate did not really work) just use the type. If you want to print it you can just use .ToString("d") (here are other formats: http://msdn.microsoft.com/en-us/library/zdtaw1bw.aspx). It's hard to give you more hints since I don't know what you want to do with the date after you get it from the db — Pawel, Oct 15 '12 at 20:49

score 4 · Answer 1 · answered Oct 15 '12 at 21:43

In .NET, the DateTime class actually represents both a date and a time. Internally, this is stored as a numeric value represented by the number of 100-nanosecond "ticks" since Midnight, January 1, 1001 AD. This number gets "converted" when it's displayed (either in output or in a debugger). This conversion is done via a format string.

Even if you truncate a DateTime's time portion, it still has a time... it's just 00:00:00, and if you don't want to see that time, you need to adjust your format string to not convert that.

Thus, if you do something like this: DateTime.Now.Date it will display `10/15/2012 00:00:00" if you use the default date conversion string (or whatever is the default format for your culture).

If you want to only display the Date portion, then you must do something like myDate.ToShortDateString() or myDate.ToString("d").

EntityFunctions is a set of tools designed to be used in Linq to Entities queries, because doing DateTime formatting is not normally allowed in a query.

For example, this code does not work:

var q = from x in dc where x.BirthDate == DateTime.Now.AddYears(-15).Date select x;

You have to do it like this:

var q = from x in dc 
    where x.Birthdate == EntityFunctions.TruncateTime(DateTime.Now.AddYears(-15)) 
    select x;

This will then generate the correct SQL to do date comparisons in SQL code. This is what the EntityFunctions are designed for, not truncating dates in the select portion (although it does work). But, even though the date is truncated, it will still have a Time component, it will just be 00:00:00, and you must use a date format string to present it to your users in the manner you intend.

EntityFunctions are deprecated use DbFunctions.TruncateTime instead — sanjeev40084, May 05 '16 at 21:15
@sanjeev40084 - that depends on what version of EF you're using. If you're using EF6 or greater, yes. But not if you're using an older version. — Erik Funkenbusch, May 05 '16 at 21:54

dansasu11 · Answer 2 · 2012-10-15T19:17:16.810

0

cant you use ToShortDateString() like below?

       List<DateTime> time = new List<DateTime>();

        time.Add(DateTime.Now);

       var WhatDate = from date in time
       select new { Date = date.ToShortDateString() };

In your case try this

            From d In Request
           Where d.Requestor= "XXXX" And d.ProcessedFlag = "N"
          Select new{ RequestID =  d.RequestID, RequestReason =   d.RequestReason.ItemValue,    RequestType =  d.RequestType.ItemValue, RequestedDate =  d.RequestedMoveDate.ToShortDateString()};

edited Oct 15 '12 at 19:17

answered Oct 15 '12 at 18:25

dansasu11

875
1
9
17

I tired this and it didnt work and I get this error 'ToShortDateString' is not a member of 'Date?' – Aradana Oct 15 '12 at 18:43
sorry, didnt see your comments, Requestedmovedate is datetime – Aradana Oct 15 '12 at 19:04
I tired so many variations of this, date.date etc and I found this is a bug until version EF 3.5 and in EF 4.0 they have this Entityfunctions that is supposed to solve the issue but not in my case. – Aradana Oct 15 '12 at 19:07
Thanks for trying to help me. I got the same error and I found a work around with this issue. I actually got the date as is from the database and changed the format in the gridview. – Aradana Oct 15 '12 at 19:51
@Aradana - That's not a "workaround" that's how it's supposed to work. As has already been said, There's no such thing as a DateTime that does not have a time. When you use TruncateTime it just makes the time 00:00:00. It's your responsibility to choose how you want to DISPLAY the DateTime, and using a formatting string to only display the date is the correct way to do it. The EntityFunctions are used in other parts of the query, because you couldn't use (for example) DateTime.Date in a Where clause. It's not intended to be used in the select statement (though it can be). – Erik Funkenbusch Oct 15 '12 at 21:27

EntityFunction.TruncateTime not working in my query

2 Answers2