CODEWITHSUNDEEP
c#sql
LitteringAnd
LitteringAnd

Reputation: 69

Using DateDiff with with null value

I'm working in C# with an SQL statement below. 

string ConsultDiff = 
  @"SELECT AVG(ISNULL(DATEDIFF(DAY, Consult_Date, Start_Date),0)) 
     FROM patients 
    WHERE progression = 'CO' AND Plan_Type = 'New Patient' " + 
     Site + " " + Primary_Onc + " " + Type + " " + DateRange + "";

When there are no rows available due to the data filtering, it throws the error: 'Object cannot be cast from DBNull to other types'. I think it is having a problem with the DATEDIFF section. I want it to show a value of 0 when there is a null value. It seems to still return a null value. The code works fine when there are actual rows to pull data from. the code is called in the following part of c#:

using (SqlCommand cmd3 = new SqlCommand(ConsultDiff, connection))
        {
            connection.Open();
            int countConsultDiff = Convert.ToInt32(cmd3.ExecuteScalar());
            lblConsulttoStart.Text = Convert.ToString(countConsultDiff);
            connection.Close();
        }

Upvotes: 0

Views: 440

Answers (3)

nitin.sharma0180
nitin.sharma0180

Reputation: 471

Your database field contain SQL NULL values when they are returned via a query due to which it is throwing Exception/Error .You can check and use: DBNull.Value and incase it is returning Null value then do no "Select or Execute the query " . You need to check the returned value to ensure it is not DBNull. You can also allow null by declare the variable as nullable types.

Upvotes: 2

bruno.almeida
bruno.almeida

Reputation: 2896

Try make this:

var result = cmd3.ExecuteScalar();
int countConsultDiff = Convert.IsDBNull(result) ? 0 : Convert.ToInt32(result);

Upvotes: 2

AntDC
AntDC

Reputation: 1917

You could use COALESCE. I am not sure if bothe can be null or not but lets assume only ever 1 will be null, ou should get it from that.

DATEDIFF(DAY, COALESCE(Consult_Date, StartDate), COALESCE(Start_Date, Consult_Date)

So in the above, I believe if eitehr ar null, it will coealesce to the others value and hence the DATEDIFF Will be zero.

Hope it works and helps

Upvotes: 0

Related Questions