DateTime

I have a vb.net program to read DATETIME column from MSSQL and write to a
TXT file.
And I notice that the DATETIME format in the TXT file changed according to
my machine datetime setting in Regional And Language Option.
Why is that so? How do I prevent that?

Using vODBCConnection As New System.Data.Odbc.OdbcConnection
vODBCConnection.ConnectionString = "Driver={SQL
Server};Server=Jumbo;Trusted_Connection=No;Database=DBIAS;uid=developer;
pwd=password"
vODBCConnection.Open()

Dim vODBCCommand As New System.Data.Odbc.OdbcCommand
Dim vODBCDataReader As System.Data.Odbc.OdbcDataReader

vODBCCommand.CommandText = "SELECT * FROM Student"
vODBCCommand.CommandType = CommandType.Text
vODBCCommand.Connection = vODBCConnection
vODBCDataReader = vODBCCommand.ExecuteReader()

Dim vStreamWriterOutput As System.IO.StreamWriter = New
System.IO.StreamWriter("c:\SQL Student.csv")
Dim vRecordCount As Integer
vRecordCount = 0
Do While vODBCDataReader.Read()

Dim i As Integer, vRow As String = ""
For i = 1 To vODBCDataReader.FieldCount

vRow += vODBCDataReader(i - 1).ToString + Trim(",")

Next

vStreamWriterOutput.WriteLine(vRow)
vRecordCount += 1
Loop
vStreamWriterOutput.WriteLine(vRecordCount)

vStreamWriterOutput.Close()
vStreamWriterOutput.Dispose()

vODBCConnection.Close()
vODBCConnection.Dispose()

End Using

TBoon wrote:
> I have a vb.net program to read DATETIME column from MSSQL and write
> to a TXT file.
> And I notice that the DATETIME format in the TXT file changed
> according to my machine datetime setting in Regional And Language
> Option. Why is that so? How do I prevent that?

Search for "Writing International Transact-SQL Statements" in the SQL Server
Books Online.

Basically, in your SQL you need to CONVERT the datetime to a string in the
format you require (I suggest 112 as that's yyyymmdd).

Any particular reason you're not using an SqlConnection?

Andrew

TBoon,

Your DateTime in SQL server is stored as Ticks (milliseconds/3) starting at
somewhere 1753 (the starting date of the Gregorian Calendar in the British
Empire and its colonies). Therefore you date from the SQL server is not
changed.

Your ToString function is for the DateTime structure overloaded and
therefore it converts to the normal setting for the culture which are set in
the computer.

If you want a special format than you can use the DateTime.ParseExact

http://msdn2.microsoft.com/en-us/li...parseexact.aspx


Cor

"TBoon" <allblacks15@hotmail.com> schreef in bericht
news:eboDS8rHHHA.1008@TK2MSFTNGP06.phx.gbl...
>I have a vb.net program to read DATETIME column from MSSQL and write to a
>TXT file.
> And I notice that the DATETIME format in the TXT file changed according to
> my machine datetime setting in Regional And Language Option.
> Why is that so? How do I prevent that?
>
> Using vODBCConnection As New System.Data.Odbc.OdbcConnection
> vODBCConnection.ConnectionString = "Driver={SQL
> Server};Server=Jumbo;Trusted_Connection=No;Database=DBIAS;uid=developer;
> pwd=password"
> vODBCConnection.Open()
>
> Dim vODBCCommand As New System.Data.Odbc.OdbcCommand
> Dim vODBCDataReader As System.Data.Odbc.OdbcDataReader
>
> vODBCCommand.CommandText = "SELECT * FROM Student"
> vODBCCommand.CommandType = CommandType.Text
> vODBCCommand.Connection = vODBCConnection
> vODBCDataReader = vODBCCommand.ExecuteReader()
>
> Dim vStreamWriterOutput As System.IO.StreamWriter = New
> System.IO.StreamWriter("c:\SQL Student.csv")
> Dim vRecordCount As Integer
> vRecordCount = 0
> Do While vODBCDataReader.Read()
>
> Dim i As Integer, vRow As String = ""
> For i = 1 To vODBCDataReader.FieldCount
>
> vRow += vODBCDataReader(i - 1).ToString + Trim(",")
>
> Next
>
> vStreamWriterOutput.WriteLine(vRow)
> vRecordCount += 1
> Loop
> vStreamWriterOutput.WriteLine(vRecordCount)
>
> vStreamWriterOutput.Close()
> vStreamWriterOutput.Dispose()
>
> vODBCConnection.Close()
> vODBCConnection.Dispose()
>
> End Using
>