Reading a date using DataReader

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"];
}

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.

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.

 (DateTime)MyReader["ColumnName"];

OR

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

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!

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");
}

        /// <summary>
    /// Returns a new conContractorEntity instance filled with the DataReader's current record data
    /// </summary>
    protected virtual conContractorEntity GetContractorFromReader(IDataReader reader)
    {
        return new conContractorEntity()
        {
            ConId = reader["conId"].ToString().Length > 0 ? int.Parse(reader["conId"].ToString()) : 0,
            ConEmail = reader["conEmail"].ToString(),
            ConCopyAdr = reader["conCopyAdr"].ToString().Length > 0 ? bool.Parse(reader["conCopyAdr"].ToString()) : true,
            ConCreateTime = reader["conCreateTime"].ToString().Length > 0 ? DateTime.Parse(reader["conCreateTime"].ToString()) : DateTime.MinValue
        };
    }

OR

        /// <summary>
    /// Returns a new conContractorEntity instance filled with the DataReader's current record data
    /// </summary>
    protected virtual conContractorEntity GetContractorFromReader(IDataReader reader)
    {
        return new conContractorEntity()
        {
            ConId = GetValue<int>(reader["conId"]),
            ConEmail = reader["conEmail"].ToString(),
            ConCopyAdr = GetValue<bool>(reader["conCopyAdr"], true),
            ConCreateTime = GetValue<DateTime>(reader["conCreateTime"])
        };
    }

// Base methods
        protected T GetValue<T>(object obj)
    {
        if (typeof(DBNull) != obj.GetType())
        {
            return (T)Convert.ChangeType(obj, typeof(T));
        }
        return default(T);
    }

    protected T GetValue<T>(object obj, object defaultValue)
    {
        if (typeof(DBNull) != obj.GetType())
        {
            return (T)Convert.ChangeType(obj, typeof(T));
        }
        return (T)defaultValue;
    }

In my case I changed the datetime field in the SQL database to not allow null. SqlDataReader then allowed me to cast the value directly to a DateTime.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Library
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\NIKHIL R\Documents\Library.mdf;Integrated Security=True;Connect Timeout=30");
            string query = "INSERT INTO [Table] (BookName , AuthorName , Category) VALUES('" + textBox1.Text.ToString() + "' , '" + textBox2.Text.ToString() + "' , '" + textBox3.Text.ToString() + "')";
            SqlCommand com = new SqlCommand(query, con);
            con.Open();
            com.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Entry Added");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\NIKHIL R\Documents\Library.mdf;Integrated Security=True;Connect Timeout=30");
            string query = "SELECT * FROM [TABLE] WHERE BookName='" + textBox1.Text.ToString() + "' OR AuthorName='" + textBox2.Text.ToString() + "'";
            string query1 = "SELECT BookStatus FROM [Table] where BookName='" + textBox1.Text.ToString() + "'";
            string query2 = "SELECT DateOfReturn FROM [Table] where BookName='" + textBox1.Text.ToString() + "'";
            SqlCommand com = new SqlCommand(query, con);
            SqlDataReader dr, dr1,dr2;
            con.Open();
            com.ExecuteNonQuery();
            dr = com.ExecuteReader();

            if (dr.Read())
            {
                con.Close();
                con.Open();
                SqlCommand com1 = new SqlCommand(query1, con);
                com1.ExecuteNonQuery();
                dr1 = com1.ExecuteReader();
                dr1.Read();
                string i = dr1["BookStatus"].ToString();
                if (i =="1" )
                {
                    con.Close();
                    con.Open();
                    SqlCommand com2 = new SqlCommand(query2, con);
                    com2.ExecuteNonQuery();
                    dr2 = com2.ExecuteReader();
                    dr2.Read();


                        MessageBox.Show("This book is already issued\n " + "Book will be available by "+ dr2["DateOfReturn"] );

                }
                else
                {
                    con.Close();
                    con.Open();
                    dr = com.ExecuteReader();
                    dr.Read();
                   MessageBox.Show("BookFound\n" + "BookName=" + dr["BookName"] + "\n AuthorName=" + dr["AuthorName"]);
                }
                con.Close();
            }
            else
            {
                MessageBox.Show("This Book is not available in the library");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\NIKHIL R\Documents\Library.mdf;Integrated Security=True;Connect Timeout=30");
            string query = "SELECT * FROM [TABLE] WHERE BookName='" + textBox1.Text.ToString() + "'";
            string dateofissue1 = DateTime.Today.ToString("dd-MM-yyyy");
            string dateofreturn = DateTime.Today.AddDays(15).ToString("dd-MM-yyyy");
            string query1 = "update [Table] set BookStatus=1,DateofIssue='"+ dateofissue1 +"',DateOfReturn='"+ dateofreturn +"' where BookName='" + textBox1.Text.ToString() + "'";
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            SqlDataReader dr;
            com.ExecuteNonQuery();
            dr = com.ExecuteReader();
            if (dr.Read())
            {
                con.Close();
                con.Open();
                string dateofissue = DateTime.Today.ToString("dd-MM-yyyy");
                textBox4.Text = dateofissue;
                textBox5.Text = DateTime.Today.AddDays(15).ToString("dd-MM-yyyy");
                SqlCommand com1 = new SqlCommand(query1, con);
                com1.ExecuteNonQuery();
                MessageBox.Show("Book Isuued");
            }
            else
            {
                MessageBox.Show("Book Not Found");
            }
            con.Close();

        }

        private void button4_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\NIKHIL R\Documents\Library.mdf;Integrated Security=True;Connect Timeout=30");
            string query1 = "update [Table] set BookStatus=0 WHERE BookName='"+textBox1.Text.ToString()+"'";
            con.Open();
            SqlCommand com = new SqlCommand(query1, con);
            com.ExecuteNonQuery();
            string today = DateTime.Today.ToString("dd-MM-yyyy");
            DateTime today1 = DateTime.Parse(today);
            string query = "SELECT dateofReturn from [Table] where BookName='" + textBox1.Text.ToString() + "'";
            con.Close();
            con.Open();
            SqlDataReader dr;
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.ExecuteNonQuery();
            dr = cmd.ExecuteReader();
            dr.Read();
            string DOR = dr["DateOfReturn"].ToString();
            DateTime dor = DateTime.Parse(DOR);
            TimeSpan ts = today1.Subtract(dor);
            string query2 = "update [Table] set DateOfIssue=NULL, DateOfReturn=NULL WHERE BookName='" + textBox1.Text.ToString() + "'";
            con.Close();
            con.Open();
            SqlCommand com2 = new SqlCommand(query2, con);
            com2.ExecuteNonQuery();
            int x = int.Parse(ts.Days.ToString());
            if (x > 0)
            {
                int fine = x * 5;
                textBox6.Text = fine.ToString();
                MessageBox.Show("Book Received\nFine=" + fine);
            }
            else
            {
                textBox6.Text = "0";
                MessageBox.Show("Book Received\nFine=0");
            }
                con.Close();
        }
    }
}