DateTime

[TBoon]

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

 

[Andrew Morton]

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

 

[Cor Ligthert [MVP]]

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/library/system.datetime.parseexact.aspx


Cor