Solved

String Format for DateTime?

Posted on 2013-06-07
8
313 Views
Last Modified: 2013-06-10
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
Comment
Question by:Ahelbling
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Expert Comment

by:adriankohws
ID: 39231035
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
 
LVL 20

Expert Comment

by:informaniac
ID: 39232501
I would guess that you are getting blanks/null in the date column.
0
 
LVL 10

Expert Comment

by:adriankohws
ID: 39232813
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
 

Author Comment

by:Ahelbling
ID: 39233535
Any suggestions on how to accomplish this?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 20

Expert Comment

by:informaniac
ID: 39233643
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
 

Author Comment

by:Ahelbling
ID: 39233651
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
 
LVL 10

Accepted Solution

by:
adriankohws earned 500 total points
ID: 39233654
This doesn't work for you?

Datestring = Format(CDate(stDataReader2(i)), "M/d/yyyy")
0
 

Author Comment

by:Ahelbling
ID: 39233663
I will try that - thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now