• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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?

SS
            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"
            Else
                '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
                            sb.Append(stDataReader2(i).ToString())
                        End If
                        i = i + 1
                    End While
                    sw.WriteLine(sb.ToString())
                End While
            End If

            'close StreamWriter
            sw.Close()

            'close dataREADER
            stDataReader2.Close()

Open in new window


TIA,
Andrew
0
Ahelbling
Asked:
Ahelbling
  • 3
  • 3
  • 2
1 Solution
 
adriankohwsCommented:
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")
0
 
informaniacCommented:
I would guess that you are getting blanks/null in the date column.
0
 
adriankohwsCommented:
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
AhelblingAuthor Commented:
Any suggestions on how to accomplish this?
0
 
informaniacCommented:
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.
0
 
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
0
 
adriankohwsCommented:
This doesn't work for you?

Datestring = Format(CDate(stDataReader2(i)), "M/d/yyyy")
0
 
AhelblingAuthor Commented:
I will try that - thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now