Nullable DateTime values from SqlDataReader

Question

How do we read integer null values from SqlDataReader?

CREATE TABLE [dbo].[Mem_Basic] (
  [Id]          INT           IDENTITY (1, 1) NOT NULL,
  [Mem_Email]   VARCHAR (50)  NULL,
  [Mem_DOB]     DATE          NULL,
  [Mem_ResPin]  INT           NULL,
  PRIMARY KEY CLUSTERED ([Id] ASC)
);

For string and integer I use

if (reader.Read() == true)
{
  mb.Mem_Email = reader["Mem_Email"] == System.DBNull.Value ? null : (string)reader["Mem_Email"];
  mb.Mem_ResPin = reader["Mem_ResPin"] as int? ?? default(int);
 }

But how do I read the date in same manner from reader?

//mb.Mem_DOB = (Convert.ToDateTime(reader["Mem_DOB"]));

i cant't read date value from database of there is know value is there (null value). If there is null i want return null value — neel
– neel, Commented Sep 2, 2013 at 4:52
If it's a nullable DateTime in the DB, it should be a nullable DateTime (DateTime?) in the data access layer. — Corak
– Corak, Commented Sep 2, 2013 at 4:52

Matt Johnson-Pint · Accepted Answer · 2013-09-02 05:05:48Z

4

mb.Mem_DOB = reader["Mem_DOB"] == DBNull.Value
                 ? (DateTime?) null
                 : (DateTime) reader["Mem_DOB"];

The mb.Mem_DOB should be declared as a DateTime?

Don't use Convert.ToDateTime. The value is already a DateTime, so just cast it.

answered Sep 2, 2013 at 5:05

Matt Johnson-Pint

244k75 gold badges469 silver badges621 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

neel Over a year ago

@Matt-shows the error there is no impicit convertion between <null> and System. DateTime

Jeremy Thompson · Accepted Answer · 2013-09-02 04:53:38Z

2

DateTime? Mem_DOB = null;

mb.Mem_DOB = reader["Mem_DOB"] == System.DBNull.Value ? null : Convert.ToDateTime(reader["Mem_DOB"]);

answered Sep 2, 2013 at 4:53

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

8 Comments

Corak Over a year ago

Does that compile, or do you need (DateTime?)null?

Corak Over a year ago

@Parvathiiiii - what is the type of Mem_DOB?

Jeremy Thompson Over a year ago

Mem_DOB is a nullable DateTime member of your class mb and its also the name of a column in your database table.

Jeremy Thompson Over a year ago

public DateTime? Mem_DOB { get; set; } - nullable with the question mark:)

Corak Over a year ago

@Parvathiiiii DateTime can not hold a null value. DateTime? can. Notice the question mark at the end. But changing that means, everywhere you use it, you need to check for HasValue and then use the Value.

|

Dmitrii Dovgopolyi · Accepted Answer · 2013-09-02 04:51:34Z

1

mb.Mem_DOB = reader["Mem_DOB"] == System.DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["Mem_DOB"]);

answered Sep 2, 2013 at 4:51

Dmitrii Dovgopolyi

6,2972 gold badges29 silver badges44 bronze badges

2 Comments

Jeremy Thompson Over a year ago

SQLDateTime for 1753, not DateTime.MinValue as 1/1/0001 as dates before 1753 wont work being reinserted into SQL.

Dmitrii Dovgopolyi Over a year ago

I asume that Mem_DOB is DateTime, not DateTime?. In this case we should read it this way and write to database Null if Mem_DOB == DataTime.MinValue

Ehsan · Accepted Answer · 2013-09-02 05:00:04Z

0

You can check if it is null or not.

int i = reader.GetOrdinal("Mem_DOB")
if( reader.IsDBNull(i))
   mb.Mem_DOB = someDefaultValueforyourdatetime; //assign null of Mem_DOB is nullable
else
   mb.Mem_DOB = Convert.ToDateTime(reader(i));

Though instead of using Convert.ToDateTime directly, i would recommend using DateTime.TryParse or DateTime.TryParseExact.

edited Sep 2, 2013 at 5:00

answered Sep 2, 2013 at 4:54

Ehsan

32.9k6 gold badges61 silver badges66 bronze badges

1 Comment

marc_s Over a year ago

That will only work if Mem_DOB is a nullable DateTime? field of your class....

Collectives™ on Stack Overflow

Nullable DateTime values from SqlDataReader

4 Answers 4

1 Comment

8 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

8 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related