Compare DateTime null value in SQL and in C#

Question

I am trying to use compare dates in a query. I have d and p are rows selected from different datatables.I want to retrieve fields from datatables based on condition. I have below conditions but throwing an error when any one of the below date is Null or empty.

I tried below statements-

join d in Data.AsEnumerable()
                    on p.Field<String>("EMPLID") equals
                    d.Field<String>("EMPLID")
 where (Convert.ToString(d.Field<DateTime>("CONTRACT_START"))!=null) &&  (Convert.ToString(d.Field<DateTime>("SIGNED_DT"))!=null) && (Convert.ToString(d.Field<String>("ACT_DT"))!=null)

Throwing an error- Can not cast DBNull.Value to Sysytem .date Time . Please use a nullable types.

when above condition is true( values are not null then I want to exceute below statement in a query)

 where (d.Field<DateTime>("CONTRACT_START") >= sd && d.Field<DateTime>("CONTRACT_START") <= ed) ||Convert.ToString(d.Field<DateTime>("SIGNED_DT"))!=" " && d.Field<DateTime>("SIGNED_DT") > d.Field<DateTime>("CONTRACT_START") || d.Field<DateTime>("ACT_DT") > d.Field<DateTime>("CONTRACT_START"))

How to check if the values are not null then execute the condition using in a query in C#.

Rahul · Accepted Answer · 2017-06-16 13:39:04Z

1

Yes, that's cause your value is null in your datatable. You should change the line in your where condition to be (notice the nullable DateTime DateTime?)

(Convert.ToString(d.Field<DateTime?>("CONTRACT_START"))!=null)

answered Jun 16, 2017 at 13:39

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

5 Comments

user2897967 Over a year ago

I tried this but still getting an errorjoin d in Data.AsEnumerable() on p.Field<String>("EMPLID") equals d.Field<String>("EMPLID")

user2897967 Over a year ago

(Convert.ToString(d.Field<DateTime?>("CONTRACT_START"))!=null && (Convert.ToString(d.Field<DateTime?>("SIGNED_DT"))!=null) && (Convert.ToString(d.Field<DateTime?>("GRANTACT_DT"))!=null)) where (d.Field<DateTime?>("CONTRACT_START") >= sd && d.Field<DateTime?>("CONTRACT_START") <= ed) || (d.Field<DateTime?>("SIGNED_DT") > hcmd.Field<DateTime?>("CONTRACT_START")

user2897967 Over a year ago

Can not cast DBNull.Value to System.DateTime

user2897967 Over a year ago

Is there any way we can add 1/1/1900 date value if it is null and then check if that date is > sd in query itself.

user2897967 Over a year ago

It worked i am not getting any errors . But just want to understand whow exactly < DateTime? > is checking null value in (Convert.ToString(d.Field<DateTime?>("CONTRACT_START"))!=null)

N. M. · Accepted Answer · 2017-06-16 13:40:23Z

1

I'm not proficient in MySql, but you are definately using DateTime the wrong way. In c# DateTime is struct, which means it cannot be null.

You have to use Nullable dates in your c# project instead (MSDN: Using Nullable Types):

DateTime? dateTime;

edited Jun 16, 2017 at 13:40

answered Jun 16, 2017 at 13:34

N. M.

871 gold badge1 silver badge6 bronze badges

Collectives™ on Stack Overflow

Compare DateTime null value in SQL and in C#

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related