DateTime

13 Dec 2006 2:01 PM

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

13 Dec 2006 2:41 PM

Andrew Morton

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

14 Dec 2006 5:05 AM

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

Show quoteHide quote

"TBoon" <allblack***@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
>