Skip to content

Use to_date/to_timestamp for translating string to date-format #2505

@WhatzGames

Description

@WhatzGames

Recently I had some data where I had to work with dates.
Sadly a column was not in any date type but simply string.

Now in my case, it was still in ISO1806- format and just casting to object, and then DateTime created a good enough query.

But when needed, giving a custom format is, as far as I know, not supported in translation.

I created a test case that seemed somewhat useful as well.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var ctx = new StudentContext(args[0]);
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

var results = await ctx.Student
         .Select(x => DateTime.ParseExact(x.DateOfBirth, "dd.MM.yyyy", null))
         .GroupBy(x => x.Year < 1960
              ? "seniors"
              : x.Year >= 2000
                  ? "youth"
                  : "adults")
         .Select(x => new
          {
              Group = x.Key,
              Amount = x.Count()
          }).ToArrayAsync();
    
Console.WriteLine(results);

public class StudentContext : DbContext
{
    private readonly string _connectionString;
    public DbSet<Student> Student { get; set; }

    public StudentContext(string connectionString)
    {
        _connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
           .UseNpgsql(_connectionString)
          .LogTo(Console.WriteLine, LogLevel.Information)
          .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>()
                    .HasData(
                         new Student {Id = 1, DateOfBirth = "01.01.1989"},
                         new Student {Id = 2, DateOfBirth = "15.01.1993"},
                         new Student {Id = 3, DateOfBirth = "29.02.2005"},
                         new Student {Id = 4, DateOfBirth = "04.12.1956"});
    }
}

public class Student
{
    public long Id { get; set; }
    public string DateOfBirth { get; set; }
}

And I expected a result in the following manner:

SELECT CASE
    WHEN date_part('year', to_date("Student"."DateOfBirth", 'dd.MM.yyyy')) < 1960 THEN 'seniors'
    WHEN date_part('year', to_date("Student"."DateOfBirth", 'dd.MM.yyyy')) >= 2000 THEN 'youth'
    ELSE 'adults'
    END AS groupName, count(*) as amount FROM "Student"
GROUP BY groupName

But instead, it throws me an exception about not being able to translate the query.

Now, since I could not find a simple DateTime.Parse(string input, string format) method that one could work with.
I'd suggest providing an extension:

Ef.Functions.ToDate(string input, string format);

Which then would get translated instead

Metadata

Metadata

Assignees

Labels

No labels
No labels

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions