String Format for DateTime?

Can somebody please help me understand why I am getting this error when I try to format a smalldatetime field from SQL to a string formatted date time?

            Dim selectCommand6 As New SqlCommand("SELECT BarCode, BeginDate, Species, Notes, Owner, WaterCode FROM StakePrint WHERE Owner=@UID", stConPrint)
            selectCommand6.Parameters.AddWithValue("UID", sUID)

            Dim sw As New StreamWriter(HttpContext.Current.Server.MapPath("LabelFiles/Stakes/test.txt"))

            Dim stDataReader2 As SqlDataReader = selectCommand6.ExecuteReader
            'any rows returned?
            If Not stDataReader2.HasRows Then
                'no rows returned
                'lblTest2.Text = "NO RECORDS"
                'rows returned
                'lblTest2.Text = "RECORDS"
                Dim fields As Integer = stDataReader2.FieldCount - 1
                Dim sFieldName As String
                Dim sep As String = ","
                While stDataReader2.Read()
                    Dim i As Integer = 0
                    Dim sb As New StringBuilder()
                    While i <= fields
                        sFieldName = stDataReader2.GetName(i)
                        If sFieldName = "BarCode" Then
                            sb.Append(stDataReader2(i).ToString() + sep)
                        ElseIf sFieldName = "BeginDate" Then
                            sb.Append(stDataReader2(i).ToString("M/d/yyyy") + sep)
                        ElseIf sFieldName = "Notes" Then
                            sb.Append(stDataReader2(i).ToString() + sep)
                        ElseIf sFieldName = "Owner" Then
                            sb.Append(stDataReader2(i).ToString() + sep)
                        ElseIf sFieldName = "WaterCode" Then
                        End If
                        i = i + 1
                    End While
                End While
            End If

            'close StreamWriter

            'close dataREADER

Open in new window

Who is Participating?
adriankohwsConnect With a Mentor Commented:
This doesn't work for you?

Datestring = Format(CDate(stDataReader2(i)), "M/d/yyyy")
Because when you get datetime from SQL database, it is going to be with date and time displaying time as many zeros with semi-colons. System won't identify as date in VB.

Do this:
Dim Datestring as string
Datestring = Format(CDate(stDataReader2(i)), "M/d/yyyy")
Obadiah ChristopherCommented:
I would guess that you are getting blanks/null in the date column.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Looking at the error message, there's data, just that datatype was not correct as expected and this way of format doesn't work and resulted that the string converted, instead of a date is actually ‘M/d/yyyy' and system attempted to cast as integer.
AhelblingAuthor Commented:
Any suggestions on how to accomplish this?
Obadiah ChristopherCommented:
What dateformat is returned by the database? Is it dd-mm-yyyy?

If it is it is possible tht you are getting a date greater than 12.
AhelblingAuthor Commented:
Exactly what is stored in the SQL table is what is displayed without any formatting performed.  It is a smalldatetime field.  Date-hours-mins-seconds
AhelblingAuthor Commented:
I will try that - thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.