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

What is the SQL **data type** of date column? – abatishchev Apr 11 '11 at 09:38 — abatishchev, Apr 11 '11 at 09:38

score 45 · Accepted Answer · edited Apr 11 '11 at 09:38

45

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.

edited Apr 11 '11 at 09:38

abatishchev

92,232
78
284
421

answered Apr 11 '11 at 09:27

Sukhjeevan

3,288
7
41
84

Error on the ConvertTo. Does not exist in current context. – TeaDrinkingGeek Apr 11 '11 at 09:29

Richard · Answer 2 · 2014-02-23T10:04:54.330

21

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.

edited Feb 23 '14 at 10:04

answered Apr 11 '11 at 09:46

Richard

100,436
21
189
251

GetDateTime accepts an integer only: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getdatetime%28v=vs.110%29.aspx – sikander Feb 23 '14 at 04:33
@sikander I can only claim I am consistent in forgetting to use `GetOrdinal`. – Richard Feb 23 '14 at 10:05
it is silly of MS to not allow column name. Cheers for updating your answer. – sikander Feb 24 '14 at 02:43

Muhammad Akhtar · Answer 3 · 2011-04-11T10:21:23.507

13

 (DateTime)MyReader["ColumnName"];

OR

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

edited Apr 11 '11 at 10:21

answered Apr 11 '11 at 09:27

Muhammad Akhtar

50,838
36
132
186

DID YOU TRY THIS ONE Convert.ToDateTime(MyReader["ColumnName"]); ? – Muhammad Akhtar Apr 11 '11 at 09:40
Both right expression returns `DateTime`. meanwhile left expression accepts `String`. So you need to call `ToString()` anyway. – abatishchev Apr 11 '11 at 09:48
@abatishchev; yah that's obvious, I have just provide him the solution to how to convert to DateTime. I ignore that part, because I am assuming , he must know this thing – Muhammad Akhtar Apr 11 '11 at 09:55
Right. Then just delete left part to be make the code compilable :) – abatishchev Apr 11 '11 at 09:59

score 12 · Answer 4 · answered Apr 21 '15 at 13:55

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!

You can shorten this like `DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc)` so you don't have to manually juggle ticks :) — jocull, Jan 17 '17 at 16:55

score 1 · Answer 5 · answered May 02 '18 at 00:58

1

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

answered May 02 '18 at 00:58

Camilo Terevinto

26,697
6
67
99

I think accepted answer is a good answer because it's clear from the `OP`'s question: `I read a string using this format with a data reader. How can I read in a date using similar format?` What `OP` is asking is that he/she has a column in string format but wants to convert/display it in a date format. An example of that would be `SQLite` db where we enter a date in string format. Ref: [SQLite Data Types](https://www.sqlite.org/datatype3.html). – nam May 29 '20 at 01:08

ADM-IT · Answer 6 · 2013-01-25T08:59:00.393

        /// <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;
    }

score 0 · Answer 7 · answered Apr 21 '17 at 22:16

0

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.

answered Apr 21 '17 at 22:16

user2328824

1

score -2 · Answer 8 · answered Jul 16 '17 at 18:05

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

Reading a date using DataReader

8 Answers8

Linked