Reading a date using DataReader

Question

I read a string using this format with a data reader. How can I read in a date using similar format?

while (MyReader.Read())
{
    TextBox1.Text = (string)MyReader["Note"];
}

Answer 1

Try as given below:

while (MyReader.Read())
{
    TextBox1.Text = Convert.ToDateTime(MyReader["DateField"]).ToString("dd/MM/yyyy");
}

in ToString() method you can change data format as per your requirement.

Answer 2

If the query's column has an appropriate type then

var dateString = MyReader.GetDateTime(MyReader.GetOrdinal("column")).ToString(myDateFormat)

If the query's column is actually a string then see other answers.

Answer 3

 (DateTime)MyReader["ColumnName"];

OR

Convert.ToDateTime(MyReader["ColumnName"]);

Answer 4

This may seem slightly off topic but this was the post I came across when wondering what happens when you read a column as a dateTime in c#. The post reflects the information I would have liked to be able to find about this mechanism. If you worry about utc and timezones then read on

I did a little more research as I'm always very wary of DateTime as a class because of its automatic assumptions about what timezone you are using and because it is way too easy to confuse local times and utc times.

What I'm trying to avoid here is DateTime going 'oh look the computer I'm being run on is in timezone x, therefore this time must also be in timezone x, when I get asked for my values I'll reply as if I'm in that timezone'

I was trying to read a datetime2 column.

The date time you will get back from sql server will end up being of Kind.Unspecified this seems to mean it gets treated like UTC, which is what I wanted.

When reading a date column you also have to read it as a DateTime even though it has no time and is even more prone to screwing up by timezones (as it is on midnight).

I'd certainly consider this to be safer way of reading the DateTime as I suspect it can probably be modified by either settings in sql server or static settings in your c#:

var time = reader.GetDateTime(1);
var utcTime = new DateTime(time.Ticks, DateTimeKind.Utc);

From there you can get the components (Day, Month, Year) etc and format how you like.

If what you have is actually a date + a time then Utc might not be what you want there - since you are mucking around on the client you may need to convert it to a local time first (depending on what the meaning of the time is). However that opens up a whole can of worms.. If you need to do that I'd recommend using a library like noda time. There is TimeZoneInfo in the standard library but after briefly investigating it, it doesn't seem to have a proper set of timezones. You can see the list provided by TimeZoneInfo by using the method TimeZoneInfo.GetSystemTimeZones();

I also discovered sql server management studio doesn't convert times to local time before displaying them. Which is a relief!

Answer 5

I know that this is an old question, but I'm surprised that no answer mentions GetDateTime:

Gets the value of the specified column as a DateTime object.

Which you can use like:

while (MyReader.Read())
{
    TextBox1.Text = MyReader.GetDateTime(columnPosition).ToString("dd/MM/yyyy");
}