StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to check if SQLDataReader item is DateTime os String type value?
text
Body
copied!<p>I have a function that converts datareader to CSV. SQL return <strong>YYYY-MM-DD HH:MM:SS.000</strong> while .net converts it to <strong>MM/DD/YYYY</strong> value. I would like to check if the value in the datareader is DateTime and convert it back to <strong>YYYY-MM-DD HH:MM:SS.000</strong> format.</p> <p>UPDATE:</p> <p>Code:</p> <pre><code>Public Function DR_To_CSV_Stream() As Boolean Dim FirstLine As String = "" Dim retVal As Boolean m_DebugMessage = m_DebugMessage & "DR_To_CSV_Stream: " If m_Datareader.Read Then 'CREATE HEADER NAMES m_DebugMessage = m_DebugMessage & "CREATE HEADER NAMES: " For intcount = 0 To m_Datareader.FieldCount - 1 If intcount <> m_Datareader.FieldCount - 1 Then sw.Write(String.Format("""{0}"",", m_Datareader.GetName(intcount))) FirstLine = FirstLine + String.Format("""{0}"",", m_Datareader.Item(intcount)) Else sw.Write(String.Format("""{0}""", m_Datareader.GetName(intcount))) FirstLine = FirstLine + String.Format("""{0}""", m_Datareader.Item(intcount)) End If Next sw.WriteLine("") m_DebugMessage = String.Format("{0}WriteLine(FirstLine): {1}", m_DebugMessage, FirstLine) sw.WriteLine(FirstLine) Do While m_Datareader.Read() 'READ DATA AND CREATE ROWS m_DebugMessage = m_DebugMessage & "READ DATA AND CREATE ROWS: " For intcount = 0 To m_Datareader.FieldCount - 1 If intcount <> m_Datareader.FieldCount - 1 Then sw.Write(String.Format("""{0}"",", m_Datareader.Item(intcount))) Else sw.Write(String.Format("""{0}""", m_Datareader.Item(intcount))) End If Next sw.WriteLine("") Loop m_Datareader.Close() m_DebugMessage = m_DebugMessage & "m_Datareader.Close(): " m_Datareader = Nothing Else m_DebugMessage = m_DebugMessage & "m_Datareader.Read = FALSE: " End If sw.Flush() 'COMMIT LAST SECTION TO WRITER If sw.BaseStream Is Nothing Then m_DebugMessage = m_DebugMessage & " | |NOTHING IN THE STREAM: " Else m_DebugMessage = m_DebugMessage & " | |THERE IS SOMETHING IN THE STREAM: " End If m_sr = New IO.StreamReader(sw.BaseStream) m_sr.BaseStream.Seek(0, System.IO.SeekOrigin.Begin) m_ReturnStream = m_sr.BaseStream If m_ReturnStream.CanRead Then retVal = True m_DebugMessage = m_DebugMessage & " | | 2ND TIME - THERE IS SOMETHING IN THE STREAM: " Else retVal = False m_DebugMessage = m_DebugMessage & " | |NOTHING IN THE RETURN STREAM: " End If Return retVal End Function </code></pre> <p>This function converts datareader to CSV stream. In another function I convert this stream to string and write it to the File:</p> <p>File.WriteAllBytes(outboundFileFullPath, Encoding.ASCII.GetBytes(data))</p> <p>Hope that helps to understand my issue.</p> <p>Or another way would be: to write MemoryStream directly to the file. I tried that but didn't work. Thanks!</p> <p>UPDATE#2 Here's what I did based on Romil's suggestion:</p> <pre><code> Dim dateValue As Date If intcount <> m_Datareader.FieldCount - 1 Then If (DateTime.TryParse(m_Datareader.Item(intcount).ToString(), dateValue)) Then sw.Write(String.Format("""{0}"",", dateValue.ToString("yyyy-MM-dd"))) Else sw.Write(String.Format("""{0}"",", m_Datareader.Item(intcount))) End If Else sw.Write(String.Format("""{0}""", m_Datareader.Item(intcount))) End If </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload