When I load data from excel to vb text box it is added time

GBowlsby 1 Reputation point
2021-06-23T20:54:31.18+00:00

Hello, I am loading a date from an excel file that has the format of 11/29/2021 however when I load it into excel it becomes 11/29/2021/ 5:04am for an example
Below is my code.

Dim dataReader As OleDbDataReader
        Dim Path As String = "C:\Users\glennb\Documents\MyExcelFile.xlsx"

        'This connection string states that there are no headers in the Excel file (HDR=NO) as in your example, change to (HDR=YES) if there are headers.  
        Dim connectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=NO'", Path)

        'Create a connection object, and open it. It will be disposed at the end of the using statement  
        Using connection As New OleDbConnection(connectionString)

            connection.Open()

            'Create the SQL SELECT statement (note the use of $ at the end of the sheet name, this is a MUST)  


            Dim selectExpired As String = "SELECT * FROM [Sheet1$c1:A60]"

            Using selectCommand As New OleDbCommand(selectExpired, connection)
                'Execute the SELECT statement and assign to a DataReader  
                dataReader = selectCommand.ExecuteReader
                Do While dataReader.Read
                    txtExpired.AppendText(dataReader(2).ToString & vbCrLf)
                Loop

                'Close the reader  
                dataReader.Close()

            End Using

        End Using

The data shows I just do not want the time stamp which is not in my excel file

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,600 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Karen Payne MVP 35,196 Reputation points
    2021-06-23T21:43:11.953+00:00

    Cast the string value for the date time to a DateTime then format it e.g.

    Dim value = CDate("6/23/2021 2:39:40 PM")
    Debug.WriteLine(value.ToString("MM/dd/yyyy"))
    Debug.WriteLine(value.ToShortDateString())
    

    We get
    06/23/2021
    6/23/2021

    If unsure if the string can be converted use DateTime.TryParse

    0 comments No comments