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

User · Answer

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!

User · Answer

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

User · Answer

DateTime MyReader  ColumnName      OR  Convert ToDateTime MyReader  ColumnName

User · Answer

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  gt  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

User · Answer

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

User · Answer

lt summary gt          Returns a new conContractorEntity instance filled with the DataReader s current record data          lt  summary gt      protected virtual conContractorEntity GetContractorFromReader IDataReader reader                return new conContractorEntity                         ConId   reader  conId   ToString   Length  gt  0   int Parse reader  conId   ToString      0              ConEmail   reader  conEmail   ToString                ConCopyAdr   reader  conCopyAdr   ToString   Length  gt  0   bool Parse reader  conCopyAdr   ToString      true              ConCreateTime   reader  conCreateTime   ToString   Length  gt  0   DateTime Parse reader  conCreateTime   ToString      DateTime MinValue                    OR               lt summary gt          Returns a new conContractorEntity instance filled with the DataReader s current record data          lt  summary gt      protected virtual conContractorEntity GetContractorFromReader IDataReader reader                return new conContractorEntity                         ConId   GetValue lt int gt  reader  conId                 ConEmail   reader  conEmail   ToString                ConCopyAdr   GetValue lt bool gt  reader  conCopyAdr    true               ConCreateTime   GetValue lt DateTime gt  reader  conCreateTime                         Base methods         protected T GetValue lt T gt  object obj                if  typeof DBNull     obj GetType                          return  T Convert ChangeType obj  typeof T                      return default T              protected T GetValue lt T gt  object obj  object defaultValue                if  typeof DBNull     obj GetType                          return  T Convert ChangeType obj  typeof T                      return  T defaultValue

User · Answer

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

User · Answer

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

[c#] Reading a date using DataReader

Examples related to c#

Examples related to .net

Examples related to ado.net

Examples related to datareader