EF DateTime formatting

Question

I am using EF 6 code first and need to do a sql like on a dateTime in a specific format, IE something along the lines of:

my first attempt was something along the lines off:

var users = context.User
       .Where(x => x.BirthDate.ToString("dd/MMM/yyyy h:mm tt").Contains(searchTerm).ToList()

which throws an exception where as EF does not know how to convert and DateTime.ToString() to SQL, which makes sense.

the best solution I found so far was from this page: Entity Framework 6 ToString(), formatting (DateTime format), query intercept where the answer-er uses the functions: SqlFunctions.DatePart and DbFunctions.Right to produce a string that EF can perform a Contains on however for my scenario I specifically need the format to be "dd/MMM/yyyy hh:mm tt" (20/Jan/2017 08:22 am) at the moment I am struggling to get month out in the format MMM.

on a side note if there is another way of achieving this say by creating my own function extending from the DbFunctions or SqlFunctions classes, this could also do the trick.

Answer 1

To get the month at MMM format you can use:

DbFunctions.Left(SqlFunctions.DateName("month", x.BirthDate),3)

The final query may look like this:

var users = context.User
   .Where(x =>
        (DbFunctions.Right("0" + SqlFunctions.DatePart("d", x.BirthDate), 2) + "/" //day
            + DbFunctions.Left(SqlFunctions.DateName("month", x.BirthDate), 3) + "/" // month
            + SqlFunctions.DatePart("yyyy", x.BirthDate) + " "
            + DbFunctions.Right("0" + (x.BirthDate.Hour > 12 ? x.BirthDate.Hour % 12 : x.BirthDate.Hour).ToString(), 2) + ":"
            + DbFunctions.Right("0" + SqlFunctions.DatePart("mi", x.BirthDate), 2) + " "
            + (x.BirthDate.Value.Hour > 11 ? "PM" : "AM"))
        .Contains(searchTerm)
    ).ToList();

but I'm worried about performance.

Answer 2

If searching for a date column in a particular format is important, then I would suggest adding a computed column to the table to reveal the date/time as a string. The thing to watch out for with solutions like this would be performance. For smaller to moderate size tables/DBs this will be acceptable but you'll need to monitor performance and avoid using a technique like this as a crutch because it can come back to bite you as systems grow.

ALTER TABLE Users
ADD COLUMN FormattedBirthDate AS formatDate(BirthDate)

Where formatDate is a scalar function:

CREATE FUNCTION formatDate 
(
    @dateTime AS DateTime
)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @result AS VARCHAR(50)

    IF @dateTime IS NULL
      RETURN NULL

    SELECT @result = CAST(DATEPART(dd, @dateTime) AS VARCHAR) + '/' + LEFT(DATENAME(MONTH, @dateTime),3) + '/' + CAST(YEAR(@dateTime) AS VARCHAR) + ' ' + CAST(DATEPART(HOUR, @dateTime) - (12 * CEILING((DATEPART(HOUR, @dateTime) - 12)*.1)) AS VARCHAR(2)) + ':' + RIGHT('00' + CAST(DATEPART(MINUTE,@dateTime) AS VARCHAR(2)),2) + ' ' + CASE WHEN DATEPART(HOUR,@dateTime) >= 12 THEN 'PM' ELSE 'AM' END
    RETURN @result

END

This applies a static format based on what you specified. Ugly to read and there may be some optimizations there.

This exposes a new column called FormattedBirthDate which you can map in EF though you would need to take measures to ensure it is treated as read-only, and recognized as a computed column. I would not return it in ViewModels etc, only used for your search.

in User entity:

public string FormattedBirthDate { get; private set;}

and in the User entity type configuration:

Property(x => x.FormattedBirthDate)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

This will ensure that after saving changes to a birthdate that the entity's formatted birth date is refreshed. So the caveat is that if you change a birth date, the formatted birth date will not match the birth date until SaveChanges is called.