Mysql Question posted in
The official MySQL documentation can be found here.

Format specific MySQL DateTime YYYY-MM-DD in C# with Microsoft Entity Framework

I get an error when insert record in MySQL database with Microsoft Entity Framerk.

Mi table is

CREATE TABLE IF NOT EXISTS `preventive` (
  `IDPREVENTIVE` mediumint(9) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` text,
  `DATA_CRE` datetime DEFAULT NULL,
  UNIQUE KEY `preventive_unique` (`CODICE`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

My model builder is

        modelBuilder.Entity<Preventive>(entity =>
        {
            entity.HasKey(e => e.Idpreventive).HasName("PRIMARY");

            entity.ToTable("preventive");

            entity.HasIndex(e => e.Codice, "preventive_unique").IsUnique();

            entity.Property(e => e.Idpreventive)
                .HasColumnType("mediumint(9)")
                .HasColumnName("IDPREVENTIVE");
            entity.Property(e => e.Description)
                .HasColumnType("text")
                .HasColumnName("DESCRIPTION");
            entity.Property(e => e.DataCre)
                .HasColumnType("date")
                .HasColumnName("DATA_CRE")
        });

When I try to insert a record

context.Preventives.Add(edt_prev);
context.SaveChanges();

I get an error beacuse my date is wrongly formatted by Entity Framework.

SET @p0='mytext', @p1='06/02/2024'
INSERT INTO `preventive` (`DESCRIPTION`,`DATA_CRE`)
VALUES (@p0, @p1)

but MySQL accept date in this format ‘YYYY-MM-DD’, and the correct istruction is

SET @p0='mytext', @p1='2024-02-06'
INSERT INTO `preventive` (`DESCRIPTION`,`DATA_CRE`)
VALUES (@p0, @p1)

Is it possible to tell entity framework how to format the date? For example

            entity.Property(e => e.DataCre)
                .HasColumnType("date")
                .HasColumnName("DATA_CRE")
                .HasConversion("YYYY-MM-DD")

I tried using a converter, but I can’t get it to use when inserting records

Tags:

2

Answers


  1. 0

    As ProgrommingLlama has stated in the comment.
    Dates aren’t sent to the database in the format you have provided.

    I’m going to assume your Preventive.DataCre type is string.
    In this instance you’ll have to parse the string yourself and convert it how you’d like.

    Login or Signup to reply.
  2. 0

    Try to add

    .HasColumnAnnotation("DateTimeFormat", "{0:yyyy-MM-dd HH:mm:ss}"); //
    needful format

    for entity.Property(e => e.DataCre)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search