CODEWITHSUNDEEP
c#sql-serverdatetime-format
Cristian Sanchez
Cristian Sanchez

Reputation: 43

Parse string to DateTime in C# mimicking behavior of SQL Server CAST/CONVERT

I'm working with a database containing date time values stored as strings in various different formats. These values are obtained from different external sources and their formats are outside my control. It may be possible to enumerate all formats currently stored in the database, but some as-yet-unseen format may show up at some point in the future.

In the past, these values were parsed to SQL DATETIME with SQL Server's CONVERT function and then returned to a .NET application. Now the application is receiving the string instead and parsing has to take place there.

I initially used DateTime.Parse(), but that failed because it didn't handle some formats which SQL Server handles by default (such as "yyyyMMdd"). The next approach was to use the DateTime.TryParseExact overload that takes a string array for various formats. Something like:

var dateTimeString = "20210101";
var dateTimeFormatInfo = System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat;
var expectedDateTimeFormats = dateTimeFormatInfo.GetAllDateTimePatterns().ToList();
expectedDateTimeFormats.Add("yyyyMMdd");

DateTime.TryParseExact(dateTimeString, expectedDateTimeFormats.ToArray(), CultureInfo.InvariantCulture, DateTimeStyles.AllowWhiteSpaces, out var myDateTime);

This works except that it also doesn't handle certain formats, like "yyyy-mm-dd hh:mi:ss.mmm", which SQL Server handles by default. Ironically that particular example is also handled fine by DateTime.Parse().

Is there a good way to mimic the behavior of SQL Server's CONVERT(DATETIME, val) in C# to try and successfully parse at least as many formats as were being handled in the original solution where the conversion was done in the database?

I think a solution with ParseExact that includes all the formats listed here may work but I'm wondering if there's a better way. Or maybe a better approach altogether.

Upvotes: 0

Views: 396

Answers (2)

Cristian Sanchez
Cristian Sanchez

Reputation: 43

Update: @jeroen-mostert's comment about using SqlDateTime.Parse is probably a better answer than what what we wound up going with.

Original answer:

In case it helps anyone in the future, the answer wound up being to use DateTime.TryParse() and, if that failed, calling DateTime.TryParseExact() with the special formats not already supported by TryParse().

From glancing over the source code it seems that Parse/TryParse is more lenient about things like separators, such that it works even with similar formats that are not shown when enumerating dateTimeFormatInfo.GetAllDateTimePatterns(). I believe that explains why TryParseExact didn't seem to work with formats that TryParse supported, even when TryParseExact was using all the DateTime patterns provided by the DateTimeFormatInfo object.

I did also validate format support against the listed Date and Time Styles in Microsoft's docs. This solution doesn't support 100% of them, though. That is by design since some combinations can be non-deterministic, and we are thankfully confined to a specific culture.

Not a perfect solution, as pointed out in the comments, but it did the trick here.

Upvotes: 1

J.D.
J.D.

Reputation: 1023

If you have control of the schema of your database your could either create a View or a Computed Column on the Table with the FORMAT() function to format the dates in a standardized and single consistent format that C#'s DateTime can easily handle.

Please note that the FORMAT() function can be less-than performant compared to other native SQL Server functions, in certain contexts.

Upvotes: 1

Related Questions