Query from database with date range in .Net Core

Question

I receive a MySql database and one table inside it have a Date column in string format, now I need to build a .Net core server with Pomelo and EF Core and requirement is my server can query data from that table in a range of date, but because Date column of that table is in string format so I don't know how to query it, please help. Thank you!

Answer 1

You are going to have to get that string into a date in order to query it.

I would probably add a new datetime column to the table and then create a simple console app that reads in each string date, try to parse this as a datetime and save it to the new datetime column.

Then you should see how many rows have valid datetimes and correct the others

Finally, you can then query using Entity Framework

Answer 2

With database schema change

If you can (i.e. are allowed) to change the schema of the table in question, then just add a new datetime or date column, copy the data over from the old column to the new one, and drop the column:

ALTER TABLE `YourTable` ADD COLUMN `NewDateColumn` date NOT NULL;
UPDATE `YourTable` SET `NewDateColumn` = STR_TO_DATE(`OldDateColumn`,'%Y-%m-%d');
ALTER TABLE `YourTable` DROP COLUMN `OldDateColumn`;

You can run these statements just using MySQLWorkbench or the commmand line tool. Of course you first test them with a local copy, to see that everything works fine.

With value converter

If you cannot change the schema of the table, then you can still query date ranges from the database, as long as the date strings in the database are in a string format, that sorts alphabetically (e.g. YYYY-MM-DD). In that case, you can just use a value converter in your actual app code and don't need to alter the database at all:

public class SomeModel
{
    public int SomeModelId {get; set;}
    public DateTime YourDateProperty {get; set;} // <-- the type you want to use in .NET
}

public class Context : DbContext
{
    public virtual DbSet<SomeModel> SomeModels { get; set; }

    // ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SomeModel>(
            entity =>
            {
                entity.Property(e => e.YourDateProperty)
                    .HasColumnType("varchar(255)") // <-- the type it has in the database table
                    .HasConversion(
                        v => v.ToString(@"yyyy\-MM\-dd"),
                        v => DateTime.Parse(v, CultureInfo.InvariantCulture));
            });
    }
}

// Here is how a sample query in your app would look like:
var query = context.SomeModels
    .Where(m => m.YourDateProperty >= new DateTime(2020, 9, 1) &&
                m.YourDateProperty < new DateTime(2020, 9, 10))
    .ToList();

Answer 3

how to convert a string to date in mysql?

As was told here

You can Query string to date

SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y')
FROM    yourtable