Table of Contents



Introduction


This article will focus on converting a string to Date, DateTime and TimeSpan. Converting from string type to a date type commonly is met with brute force attempts to perform a conversion for many reasons ranging from believing this type of conversion is easy, all dates are in one format or the developer does not pay attention to incoming data ranging from user input to input from a file, a container or database to not recognizing culture and/or time zones. Couple this with a developer not understanding how dates are stored.

The million dollar question "Conversion from String to type Date is not valid." If this has been an issue with coding then this article is an excellent starting place to learn working with string to date and time types.

Fundamentals


The first step to understanding how to work with date types is to read Microsoft documentation which provides a solid foundation to working with date and time types. The next step working with knowledge gained from reading Microsoft documentation is to apply this knowledge by writing focused code to see how various methods work ranging from CDate, TryParse, TryParseExact, Parse and TryParseExact.

Working with CDate is fairly simple, pass a string which should represent a date and return a date.

Dim shouldBeDate = "05/18/2019"
Dim dateResult As Date = CDate(shouldBeDate)
Console.WriteLine(dateResult)

In this case the value or dateResult is 5/18/2019 12:00:00 AM. Suppose the following string is passed to CDate.
Dim shouldBeDate = "18/05/2019"
Dim dateResult As Date = CDate(shouldBeDate)
Console.WriteLine(dateResult)

An exception is thrown.

Exception thrown: 'System.InvalidCastException' in Microsoft.VisualBasic.dll
Conversion from string "18/05/2019" to type 'Date' is not valid.

The problem is that CDate in this case didn't recognize the date format where day, month, year was passed instead of month, day, year. To fix this in the case a string to represent a date is going to always come through this way use Date.ParseExact to specify the format.

Dim shouldBeDate = "18/05/2019"
Dim dateResult As Date = Date.ParseExact(shouldBeDate, "dd/MM/yyyy", CultureInfo.InvariantCulture)
Console.WriteLine(dateResult)

In the case the date might sometimes be 05/18/2019 format or 18/05/2019 a language extension can be created to handle various known formats.

Imports System.Globalization
 
Namespace LanguageExtensions
    Public Module DateTimeExtensions
        ''' <summary>
        ''' Convert string to date time using various formats
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <returns></returns>
        <Runtime.CompilerServices.Extension>
        Public Function ToDate(sender As String) As Date
            Dim format() = {"dd/MM/yyyy", "d/M/yyyy", "dd-MM-yyyy", "MM/dd/yyyy"}
            Dim dateValue As Date
 
            Date.TryParseExact(sender, format, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None, dateValue)
 
            Return dateValue
 
        End Function
    End Module
End Namespace

Now the following is possible, do the conversion right from a string.

Console.WriteLine("18/05/2019".ToDate)
Console.WriteLine("05/18/2019".ToDate)

And the following simulating reading a date from a split line when reading a file.
  
Dim dateFromLineInFile as Date = "18/05/2019".ToDate

Another example for uncommon formats which one might come across is a string representing a date as follows 20190518 (May 18th 2019), by applying a format to match the incoming string using TryParseExact will return a valid date.

Dim englishUS As New CultureInfo("en-US")
Dim dateTimeString As String = "20190518"
Dim resultingDate As Date
If Date.TryParseExact(dateTimeString, "yyyyMMdd", englishUS, DateTimeStyles.None, resultingDate) Then
    Console.WriteLine(resultingDate)
End If

Usually dates seen in this format also have a time e.g. 201905180945 (May 18th 2019 9:45 AM). To capture the time add "hhmm" to the format string.

Dim englishUS As New CultureInfo("en-US")
Dim dateTimeString As String = "201905180945"
Dim resultingDate As Date
If Date.TryParseExact(dateTimeString, "yyyyMMddhhmm", englishUS, DateTimeStyles.None, resultingDate) Then
    Console.WriteLine(resultingDate)
End If

Note in the above examples en-US culture is used, if a different culture is needed use the following method to get a culture code.

Public Function CultureList() As Globalization.CultureInfo()
    Return (
        From T In Globalization.CultureInfo.GetCultures(Globalization.CultureTypes.SpecificCultures)
        Order By T.EnglishName).ToArray
End Function

Include in this article's source code there is a custom My.Culture module which provide the method above.

Imports System.Globalization
 
Namespace My
    <ComponentModel.EditorBrowsable(ComponentModel.EditorBrowsableState.Never)>
    Partial Friend Class _Culture
        ''' <summary>
        ''' Date separator for current culture
        ''' </summary>
        ''' <returns></returns>
        Public Function DateSeparator() As String
            Return CultureInfo.CurrentCulture.DateTimeFormat.DateSeparator
        End Function
        ''' <summary>
        ''' Return the Time Separator for current culture
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function TimeSeparator() As String
            Return CultureInfo.CurrentCulture.DateTimeFormat.TimeSeparator
        End Function
        Public Function CultureList() As CultureInfo()
            Return (
                From T In CultureInfo.GetCultures(CultureTypes.SpecificCultures)
                Order By T.EnglishName).ToArray
        End Function
    End Class
    <HideModuleName()>
    Friend Module Custom_Culture
        Private ReadOnly Instance As New ThreadSafeObjectProvider(Of _Culture)
        ReadOnly Property Culture() As _Culture
            Get
                Return Instance.GetInstance()
            End Get
        End Property
    End Module
End Namespace

Usage for English United Kingdom.

Dim dateTimeString As String = "201905180945"
Dim EnglishUK = New CultureInfo(
    My.Culture.CultureList().FirstOrDefault(
        Function(cultureInfo) cultureInfo.DisplayName = "English (United Kingdom)").Name)
 
Dim theDate As Date
If Date.TryParseExact(dateTimeString, "yyyyMMddhhmm", EnglishUK, DateTimeStyles.None, theDate) Then
    Console.WriteLine(theDate)
End If

When unsure if a string can represent a date use Date.TryParse rather than what some developers might use, IsDate which can provide incorrect results when using uncommon formats although IsDate is fine for typical conversions as shown below. All three method calls will return true.

Console.WriteLine(IsDate(CDate("5/18/2019")))
Console.WriteLine(IsDate(#05/18/2019#))
Console.WriteLine(IsDate("May 18, 2019"))

Some might wonder about the last test IsDate("May 18, 2019), this works and a good reason to read the Microsoft documentation and the same holds true for TryParse.

Dim dateValue As Date
Dim dateStringValue = "May 18, 2019"
If Date.TryParse(dateStringValue, dateValue) Then
    Console.WriteLine(dateValue)
End If

If multiple formats are needed (as shown above earlier) using month names the following shows how multiple formats can be set up.

Dim dictValues As New Dictionary(Of String, CultureInfo) From {
            {"12 Mai 2019", New CultureInfo("de-DE")},
            {"12 May 2019", New CultureInfo("en-US")},
            {"12 mayo 2019", New CultureInfo("es-MX")}
        }

Run the above in a for each to, in this case get the exact same date from different formats.

For Each kvp In dictValues
    Dim dateResultValue As Date = Date.Parse(kvp.Key, kvp.Value)
Next

Presentation of dates in user interface

In this section dates are assumed valid as the values come from a database table which means the values are valid other than testing for null values which can be done with T-SQL methods. Suppose dates need to be presented in a custom format and displayed in a DataGridView or TextBox controls as per below.



This can be coded using the Binding class and subscribe to Parse and Format events. In the Format event assert the current value is a Date using Date.TryParse, if the value is not valid do nothing, if the value is valid format via in this case ToLongDateString.

''' <summary>
''' Responsible for formatting the date as a long date if
''' the value is a valid date.
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub BindingFormatting(sender As Object, e As ConvertEventArgs)
 
    If Not Date.TryParse(e.Value.ToString(), Nothing) Then
        ' we have a value that can not represent a date
        Exit Sub
    End If
 
    e.Value = CDate(e.Value).ToLongDateString
 
End Sub

Working with the Parse event assertion must be performed as the control is not a Date control such as a DateTimePicker which handles invalid values. If the value can not represent a valid date the original value stored in the data source is reset.

''' <summary>
''' Since a Date control is not being used this event will
''' determine if a valid Date has been entered, if the string
''' entered can not represent a date reset the value.
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub BindingParser(sender As Object, e As ConvertEventArgs)
 
    If Not Date.TryParse(e.Value.ToString(), Nothing) Then
        MessageBox.Show("Sorry but you entered a invalid date, resetting date.")
 
        Dim person = dataOperations.LoadData().
            FirstOrDefault(Function(p) p.Id = CType(bsPeople.Current, Person).Id)
 
        If person IsNot Nothing Then
            e.Value = person.BirthDate
        End If
 
    End If
 
End Sub

For formatting values in a DataGridView where the underlying type for the Date column is DateTime determine if the proper column is selected followed by using TryParse to validate the value, if correct format the current cell.
Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) _
    Handles DataGridView1.CellFormatting
 
    If e.ColumnIndex = BirthDateColumnIndex Then
        Dim d As Date
        If Date.TryParse(e.Value.ToString, d) Then
            e.Value = d.ToString("MM-dd-yyyy")
            e.FormattingApplied = True
        End If
    End If
End Sub

The following is all the code excluding reading data which is also in the article's source code under the project BindingData.

Imports BindingData.Classes
''' <summary>
''' Demonstrates Date formatting with Binding class
''' </summary>
Public Class Form1
    Private bsPeople As BindingSource
    Private ReadOnly dataOperations As New DataOperations
    Private birthDateColumnIndex As Integer = 0
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 
        ' load mocked data
        bsPeople = New BindingSource With {
            .DataSource = dataOperations.LoadData
        }
 
        BindingNavigator1.BindingSource = bsPeople
        DataGridView1.DataSource = bsPeople
 
        firstNameTextbox.DataBindings.Add("Text", bsPeople, "FirstName")
        lastNameTextBox.DataBindings.Add("Text", bsPeople, "LastName")
 
        SetupBirthDateBinding()
 
        ' Provides the column index to use in CellFormatting event
        birthDateColumnIndex = DataGridView1.Columns("BirthDate").Index
 
        firstNameTextbox.SelectionStart = firstNameTextbox.Text.Length
 
    End Sub
    ''' <summary>
    ''' Called from Form Load event to create a Binding object for
    ''' controlling how the BirthDate property is presented to the
    ''' user interface. This code could had been in form load, the reason
    ''' for separating this code from form load is to push focus to this
    ''' code.
    ''' </summary>
    Private Sub SetupBirthDateBinding()
        Dim binding As Binding = New Binding("Text", bsPeople, "BirthDate")
        AddHandler binding.Parse, AddressOf BindingParser
        AddHandler binding.Format, AddressOf BindingFormatting
        birthdayTextBox.DataBindings.Add(binding)
    End Sub
    ''' <summary>
    ''' Responsible for formatting the date as a long date if
    ''' the value is a valid date.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub BindingFormatting(sender As Object, e As ConvertEventArgs)
 
        If Not Date.TryParse(e.Value.ToString(), Nothing) Then
            ' we have a value that can not represent a date
            Exit Sub
        End If
 
        e.Value = CDate(e.Value).ToLongDateString
 
    End Sub
    ''' <summary>
    ''' Since a Date control is not being used this event will
    ''' determine if a valid Date has been entered, if the string
    ''' entered can not represent a date reset the value.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub BindingParser(sender As Object, e As ConvertEventArgs)
 
        If Not Date.TryParse(e.Value.ToString(), Nothing) Then
            MessageBox.Show("Sorry but you entered a invalid date, resetting date.")
 
            Dim person = dataOperations.LoadData().
                    FirstOrDefault(Function(p) p.Id = CType(bsPeople.Current, Person).Id)
 
            If person IsNot Nothing Then
                e.Value = person.BirthDate
            End If
 
        End If
 
    End Sub
 
    Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) _
    Handles DataGridView1.CellFormatting
 
        If e.ColumnIndex = birthDateColumnIndex Then
            Dim d As Date
            If Date.TryParse(e.Value.ToString, d) Then
                e.Value = d.ToString("MM-dd-yyyy")
                e.FormattingApplied = True
            End If
        End If
    End Sub
End Class

SQL Dates

The same holds true in regards to culture as per the last example above.

SELECT FORMAT(GETDATE(), 'd', 'en-us') AS English ,
       FORMAT(GETDATE(), 'd', 'de-DE') AS German ,
       FORMAT(GETDATE(), 'd', 'es-MX') AS Spanish;

Results

English          German      Spanish
5/19/2019     19.05.2019  19/05/2019

Sometimes when working with a lot of dates use TSQL SET Language, for instance to set the language to British use  

SET LANGUAGE british;

To set a name for a specific culture run the following SQL statement in SSMS (SQL-Server Management Studio).

SELECT   LanguageID = langid ,
         name ,
         alias ,
         dateformat
FROM     sys.syslanguages
ORDER BY langid;

TimeSpan

There are times when an application might need to store date and time separately in a SQL-Server database table for things like recording event times. SQL-Server has a time type which can be used for this type of application. Without having knowledge of SQL-Server data types or understand what is available in the .NET Framework a developer may take a path that is heavy, meaning using a data container such as a DataTable rather than using a light weight container such as a concrete class.

Example, a tale named Hours is a child table of events where the hours table has a primary key of type integer and a Time field. The class to store data returned from the Hours table.

Namespace Classes
    Public Class TimeSpanItem
        Public Property Id() As Integer
        Public Property TimeSpan() As TimeSpan
 
        Public Overrides Function ToString() As String
            Return $"{TimeSpan}"
        End Function
    End Class
End NameSpace

The SqlClient class, reader (SqlDataReader) can read a time field into a TimeSpan using reader.GetTimeSpan as shown below.

Imports System.Data.SqlClient
 
Namespace Classes
    Public Class TimeDataOperations
        Inherits SqlServerConnection
 
        Public Sub New()
            ' Change this to your server name or .\SQLEXPRESS
            DatabaseServer = "KARENS-PC"
            DefaultCatalog = "DateTimeDatabase"
        End Sub
        Public Function ReadHours() As List(Of TimeSpanItem)
            Dim timeList As New List(Of TimeSpanItem)
 
            Dim selectStatement As String = "SELECT id,TimeValue FROM dbo.HoursTable"
 
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
 
                    cn.Open()
 
                    Dim reader = cmd.ExecuteReader()
                    While reader.Read()
                        timeList.Add(New TimeSpanItem() With
                     {
                      .Id = reader.GetInt32(0),
                      .TimeSpan = reader.GetTimeSpan(1)
                     })
                    End While
                End Using
            End Using
 
            Return timeList
 
        End Function
    End Class
End Namespace

The List(Of TimeSpanItem) can then be used in your application such as displaying in a ListBox for selections.

Imports BaseLibrary.Classes
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) _
        Handles MyBase.Load
 
        Dim timeOperations As New TimeDataOperations
        ListBox1.DataSource = timeOperations.ReadHours()
 
    End Sub
End Class

Another possibility for working with TimeSpan, reading a text file that has delimited fields by a delimiter or positional location in a file. In this case the developer needs to ensure that the time values are valid. In the following example the aspect to read time from a file is not important, instead each element in the string array would be a time field for each line in a text file setup in a class to be used in a unit test method. Not all elements can represent a valid TimeSpan.

Namespace TestClasses
    Public Class TestBase
        Protected ReadOnly ImperfectStringArrayForConvertingToTimeSpan() As String =
                               {
                                   "0",
                                   "14",
                                   "1:2:3",
                                   "0:0:0.250",
                                   "10.20:30:40.50",
                                   "99.23:59:59.9999999",
                                   "0023:0059:0059.0099",
                                   "23:0:0",
                                   "24:0:0",
                                   "0:59:0",
                                   "0:60:0",
                                   "0:0:59",
                                   "0:0:60",
                                   "10:",
                                   "10:0",
                                   ":10",
                                   "0:10",
                                   "10:20:",
                                   "10:20:0",
                                   ".123",
                                   "0.12:00",
                                   "10.",
                                   "10.12",
                                   "10.12:00"}
    End Class
End Namespace

One idea for assertion is to read line by line and perform assertion on each time value. Instead the following language extension method will determine if each line in the text file can be converted to a valid TimeSpan. To put this in to perspective, all other fields in the text file that need validation would use a similar assertion such as validating if all values in a specific position are integers using CanConvertToIntArray presented in part one of this series.

Here is the language extension for determining if all elements in an array of strings can be converted to integers.

<Runtime.CompilerServices.Extension>
Public Function CanConvertToIntArray(sender() As String) As Boolean
    Dim testValue As Integer
    Return sender.All(Function(input) Integer.TryParse(input, testValue))
End Function

And here is the extension method for determining if all string elements are valid TimeSpan.

<Runtime.CompilerServices.Extension>
Public Function CanConvertToTimeSpanArray(sender() As String) As Boolean
    Dim testValue As TimeSpan
    Return sender.All(Function(input) TimeSpan.TryParse(input, testValue))
End Function

Looking at both they share commonality using TryParse extension method. By having an extension method for common types allows a developer to validate a text file or other input method before importing data into an application and or a database. The only exception is that with Date types, for these extension methods will need to be setup for the data format coming in, some cases DateTime.TryParse will work while other times DateTime.TryParseExact would be needed to specify the format.

Back to using the CanConvertToTimeSpanArray, the following uses the string array above to determine if all string elements can be converted by using Assert.IsFalse as not all elements can represent a valid TimeSpan.

<TestMethod()> Public Sub ValidateNotAllStringElementsCanBeConvertToTimeSpanTest()
    Assert.IsFalse(ImperfectStringArrayForConvertingToTimeSpan.CanConvertToTimeSpanArray())
End Sub

To perform an actual conversion with no regards to if a string value can be converted the following extension method will perform a conversion, if a value can not be converted the default value is used for TimeSpan which is 00:00:00, see also TimeSpan.Zero. This extension matches one for Integer found here ToIntegerPerserveArray from part one of these series along with matching extensions for Decimal and Double in the accompanying source code. 

<Runtime.CompilerServices.Extension>
Public Function ToTimeSpanPreserveArray(sender() As String) As TimeSpan()
    Return Array.ConvertAll(sender,
                            Function(input)
                                Dim value As TimeSpan
                                TimeSpan.TryParse(input, value)
                                Return value
                            End Function)
End Function

To only obtain valid time values,

<Runtime.CompilerServices.Extension>
Public Function ToTimeSpanArray(sender() As String) As TimeSpan()
    Return Array.ConvertAll(sender,
                        Function(input)
                            Dim value As TimeSpan
                            Return New With
                           {
                               .IsTimeSpan = TimeSpan.TryParse(input, value),
                               .Value = value
                           }
                        End Function).
    Where(Function(result) result.IsTimeSpan).
    Select(Function(result) result.Value).
    ToArray()
End Function

Usage performed in a unit test method.
<TestMethod()> Public Sub PreserveTimeSpanArrayElementsTest()
    Dim test = ImperfectStringArrayForConvertingToTimeSpan.ToTimeSpanPreserveArray()
    Assert.IsTrue(test.Length = 24)
    Console.WriteLine(test.TimeSpanArrayToString)
End Sub

Unit test are essential to ensuring that any extension method like the extension methods presented above work as expected prior to depending on these extension methods in a production application. 

Unit Test

(Note all test methods are included in the article's source code)

A common mistake is when a developer knows nothing about converting strings to dates and decides to learn in their application. When doing so there are many moving parts of their application that can make it difficult to get the conversion correct. 

With that it's recommended to read the Microsoft documentation, write unit test for what is being learned. For example, working with different formats from different countries. A good test method will test for both correct and incorrect results as nothing is always going to be correct in the real word.

''' <summary>
''' Demonstrates how an expected conversion may fail because
''' the assumption is incorrect for the expected result in the
''' first assertion while the second assertion is correct.
''' </summary>
<TestMethod()> Public Sub SimpleConversionFromStringToDateTest()
 
    Dim dateValueGerman = "05.11.2019"
    Dim dateValueUS = "05/11/2019"
 
    '
    ' Parse will convert to current culture
    '
    Dim resultDate As Date = Date.Parse(dateValueGerman)
    Dim test1 = resultDate.ToString("MM/dd/yyyy")
 
    '
    ' date string will not match as result1 has / as date separator
    '
    Assert.IsFalse(test1 = dateValueGerman)
 
    '
    ' This time both values match as the right date separator is there.
    '
    Assert.IsTrue(test1 = dateValueUs)
 
 
End Sub

Not only should single conversions be test but working with string array of dates.  This is done by setting up a string array of dates and a date array to compare against the string array once converted to dates. The comparison is done using SequenceEqual.

''' <summary>
''' Given a string array in another culture convert to current culture
''' using the date format for the dates in the string array, if the format
''' is not correct ParseExact will throw an exception so the developer needs
''' to know what the culture is, in this case in the string array.
''' </summary>
<TestMethod()> Public Sub ConvertingStringFromOneCultureToAnotherAsDateTest()
 
    Dim dateFormat = "MM.dd.yyyy"
 
    Dim stringDateArray =
            {
                "05.01.2019", "05.02.2019", "05.03.2019", "05.04.2019",
                "05.05.2019", "05.06.2019", "05.07.2019", "05.08.2019",
                "05.09.2019", "05.10.2019"
            }
 
    Dim expectedResults =
            {
                #05/01/2019#, #05/02/2019#, #05/03/2019#, #05/04/2019#,
                #05/05/2019#, #05/06/2019#, #05/07/2019#, #05/08/2019#,
                #05/09/2019#, #05/10/2019#
            }
 
 
    Dim resultDateArray(9) As Date
 
    For index As Integer = 0 To stringDateArray.Length - 1
        resultDateArray(index) = Date.ParseExact(stringDateArray(index), dateFormat, Nothing)
    Next
 
    Assert.IsTrue(resultDateArray.SequenceEqual(expectedResults))
 
End Sub

In the article's source code all unit test methods are categorised as per below.



Summary

By applying logic and code presented here provides a decent foundation for working with date and time types when manipulating these types in a Visual Studio solution. Although information presented is not all encompassing the information along with references to Microsoft documentation will allow developers to work with date and time types rather than using brute force code to perform various operations which many developers tend to do, use the information and be smarter by using this information provided.

See also

C# Working with SQL-Server Time type 
VB.NET Type conversions part 1

References

Standard Date and Time Format strings
Parsing Date and Time strings in .NET

Source code

https://github.com/karenpayneoregon/ConvertingTypesVisualBasic/tree/Article1