Preserving SQL DateTime format when reading from database

Question

I have a very simple tool that runs a stored procedure from a database, puts the results into a DataTable, then writes the DataTable to a file via Response. The purpose is to take the data from a table on SQL Server, then use a 3rd-party tool to upload it to an Oracle database.

The problem I encounter is that date is stored, in SQL Server, as such: 2011-05-01 00:00:00.000.

However, when I access it via my SqlDataReader and put it into my DataTable, it ends up formatting as: 5/1/2011 12:00:00 AM.

So I figured I could just explicitly parse it as an OracleDateTime. I have the following column in my DataTable:

records.Columns.Add("Date", typeof(OracleDateTime));

As well as this bit where I am reading the results:

row["Date"] = OracleDateTime.Parse(rdr["Date"].ToString());

I also tried SqlDateTime for kicks, but ultimately, I end up with the same incorrectly formatted string. I just want it to stay the same way SQL returns it in the query - how can this be done?

Answer 1

The date is not stored that way in SQL server; what you see in your ad-hoc queries is a conversion from the internal numeric storage to that format.

When you read it in, you should be converting rdr["Date"] to a DateTime.

If you want that specific format in your code, you should format it using .NET's formatters when you output it.