How to reformat DateTime values in CSV file?

Question

I have a CSV file that contains (among other fields) (nullable) DateTime values that are formatted like "2021-11-20 14:16:52.255421" I want to get rid of the milliseconds or whatever that is.

I have tried like this, but somehow the date format remains the same:

    var csvConfiguration = new CsvConfiguration(CultureInfo.InvariantCulture);
    csvConfiguration.HasHeaderRecord = true;
    csvConfiguration.Delimiter = ";";
    csvConfiguration.TrimOptions = TrimOptions.Trim;
    
    Record[] records;

    using (var reader = new StreamReader(CsvPath, Encoding.UTF8))
    using (var csv = new CsvReader(reader, csvConfiguration))
    {
        records = csv.GetRecords<Record>().ToArray();
    }

    using (var writer = new StreamWriter($@"reformatted.csv"))
    using (var csv = new CsvWriter(writer, CultureInfo.CurrentCulture))
    {
        //I have also tried with these options, but it doesn't help either
        //var options = new TypeConverterOptions { Formats = new[] { "yyyy-MM-dd HH:mm:ss" } };
        //csv.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);
        csv.WriteRecords(records.Where(x => x.Memo != null));
    }

// "mbox_id";"email_address";"created";"updated";"created_by";"memo";
class Record
{
    [Name("mbox_id")]
    public string MailboxId { get; set; }
    
    [Name("email_address")]
    public string EmailAddress { get; set;}
    
    [Name("created")]
    public DateTime? Created { get; set; }
    
    [Name("updated")]
    public DateTime? Updated { get; set; }
    
    [Name("created_by")]
    public string CreatedBy { get; set;}
    
    [Name("memo")]
    public string Memo { get; set; }
}

What am I doing wrong? I really don't understand how the CsvWriter would even know about the input format...

Answer 1

Your problem is that, since your properties are of type DateTime?, you must explicitly register a type converter for DateTime? as well as DateTime:

csv.Context.TypeConverterOptionsCache.AddOptions<DateTime?>(options);
csv.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);

Demo fiddle #1 here.

If this situation arises often, you could create an extension method for TypeConverterOptionsCache like so:

public static class TypeConverterOptionsCacheExtensions
{
    public static void AddOptionsForTypeAndNullable(this TypeConverterOptionsCache cache, Type type, TypeConverterOptions options)
    {
        if (type == null || cache == null)
            throw new ArgumentNullException();

        if (!type.IsValueType)
        {
            cache.AddOptions(type, options);
        }
        else
        {
            var underlying = Nullable.GetUnderlyingType(type) ?? type;
            var nullable = typeof(Nullable<>).MakeGenericType(underlying);
            cache.AddOptions(underlying, options);
            cache.AddOptions(nullable, options);
        }
    }


    public static void AddOptionsForTypeAndNullable<T>(this TypeConverterOptionsCache cache, TypeConverterOptions options) where T : struct
        => cache.AddOptionsForTypeAndNullable(typeof(T), options);
}

And then do:

csv.Context.TypeConverterOptionsCache.AddOptionsForTypeAndNullable<DateTime>(options);

Demo fiddle #2 here.