VB.NET Type conversions (Part 2)
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