Returning Date instead of Datetime using modelBuilder conversion

Question

I have a Datetime property in my model that looks like this:

[DataType(DataType.Date)]
[Column(TypeName = "date")] 
public DateTime SubmittedOn { get; set; }

The value is inserted as a Date into the db, without the time portion. However, the server returns it with the time portion when the value is requested like this "2020-04-03T00:00:00"

How can I use modelBuilder in my dbContext to remove the time portion when the value is returned?

modelBuilder.Entity<Model>()
              .Property(e => e.SubmittedOn)
              .HasConversion(...?)

.NET does not have a date type, only `DateTime`, so there always will be a time. — Johnathan Barclay, Apr 08 '20 at 18:25
@JohnathanBarclay, sure, but making a conversion doesn't imply changing it's data type. — jgozal, Apr 08 '20 at 18:46
You can do something like this in c# https://learn.microsoft.com/en-us/dotnet/api/system.datetime.date?view=netframework-4.8 I'm just trying to integrate it in the modelBuilder — jgozal, Apr 08 '20 at 18:47
`DateTime.Date` returns a new `DateTime` with the time portion set to `00:00`; that's what the database is returning anyway. What is the problem you have with the time? Using `[DataType(DataType.Date)]` means the time will not be displayed in the UI anyway. — Johnathan Barclay, Apr 08 '20 at 18:49
@JohnathanBarclay You can do something like date.ToString("MM/dd/yyyy") and you have a date without the time portion formatted as a string which you can send back to the client. I was hoping there was a way to do this? I'd love to tell you about my problem with the time portion but it's beyond the scope of this question so if doing this is impossible then I just have a different problem :( — jgozal, Apr 08 '20 at 19:01
Ah so you literally just want to convert the date to a string for display purposes. I'll provide an answer. — Johnathan Barclay, Apr 08 '20 at 19:13

score 1 · Answer 1 · answered Apr 08 '20 at 19:10

I believe only the date portion is being saved in your db hence the 00:00:00 time in your returned DateTime field. The problem is in JSON serialization. As far as I remember [DataType(DataType.Date)] attribute used to work for that but doesn't now for some reason (any comments on this are welcome). Now you can do two things here (Note this assumes you're using NewtonsoftJson or willing to use it in .net core 3.x. For System.Text.Json which is the default serializer in .net core 3.x you may refer to this Formatting DateTime in ASP.NET Core 3.0 using System.Text.Json):

Make a custom converter as shown below:

public class MyCustomDateConverter : IsoDateTimeConverter
{
    public MyCustomDateConverter()
    {
        //Here you can define any format that is allowed in C#.
        DateTimeFormat = "dd/MM/yyyy";
    }
}

Now back to your DateTime field where you'll actually use it

[JsonConverter(typeof(MyCustomDateConverter))]
[Column(TypeName = "date")] 
public DateTime SubmittedOn { get; set; }

So this way you can use the converter for every field that you want to.

Alternatively if you're using asp.net core, you can set a global format for all your date fields when serialized in JSON. In your Startup class add this in your ConfigureServices method

For pre .net core 3.x

services.AddJsonOptions(
            options =>
            {
                options.SerializerSettings.DateFormatString = "dd/MM/yyyy";
            }
        );

For .net core 3.x

services.AddControllers()
        .AddNewtonsoftJson(
            options =>
            {
                options.SerializerSettings.DateFormatString = "dd/MM/yyyy";
            }
        );

been getting an interesting error here, and I've been getting the same one with the answer above : `"The property 'Model.SubmittedOn' is of type 'DateTime' which is not supported by current database provider. Either change the property CLR type or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'."` — jgozal, Apr 08 '20 at 20:10
probably not an issue with your code here, but I'd welcome any help if you know what could be going on! — jgozal, Apr 08 '20 at 20:10
Let me know what versions of Postgres, Npgsql and Asp.Net core are you using? That'll help in debugging the issue. I've never had any issue with DateTime using Postgres 10 and 12, Npgsql 2.1, 2.2, 4 and Asp.Net Core 2.1 and 3.1. — long_hair_programmer, Apr 09 '20 at 07:34

Johnathan Barclay · Answer 2 · 2020-04-08T19:26:46.680

1

To convert the date from the database into a string without the time, and also convert a date string into a date to send to the database:

modelBuilder.Entity<Model>()
    .Property(e => e.SubmittedOn)
    .HasConversion(
        v => v.ToString("MM/dd/yyyy"),
        v => DateTime.Parse(v));

edited Apr 08 '20 at 19:26

answered Apr 08 '20 at 19:16

Johnathan Barclay

18,599
1
22
35

been getting an interesting error here, and I've been getting the same one with the answer below : `"The property 'Model.SubmittedOn' is of type 'DateTime' which is not supported by current database provider. Either change the property CLR type or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'."` – jgozal Apr 08 '20 at 20:08
probably not an issue with your code here, but I'd welcome any help if you know what could be going on! – jgozal Apr 08 '20 at 20:09
Very strange, what database are you using? – Johnathan Barclay Apr 08 '20 at 20:15
I'm using postgres – jgozal Apr 08 '20 at 20:16
Not sure I'm afraid; haven't used postgres much. – Johnathan Barclay Apr 08 '20 at 20:32

Returning Date instead of Datetime using modelBuilder conversion

2 Answers2