Just a quick question. I have tried so many possibilities to get the null value and value within the date column like using ISNULL, NULL, 0, but returning 0 result. I have no idea how to program to get the null value from the creation date.

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        FillDates();
    }
}

private void FillDates()
{
    txtDateS.Text = DateTime.Now.ToString("01-01-yyyy");
    txtDateE.Text = DateTime.Now.ToString("12-31-yyyy");
}

private string ConvertDate(Object obj)
{
    if (obj is DateTime)
    {
         return ((DateTime)obj).ToString("dd-MM-yyyy");
    }
    else
    {
        return obj.ToString();
    }
}

protected void AddRowItem(StringBuilder sb, SqlDataReader reader, int index)
{
    sb.Append("<tr><td>").Append(index).Append("</td>");
    sb.Append("<td>").Append(ConvertDate(reader[0])).Append("</td>");

    sb.Append("</tr>");
}   

protected int DoItemList(SqlConnection connection, StringBuilder sb, string DateS, string DateE, string filter)
{
    string sql = "";
    SqlCommand readCommand = null;
    SqlDataReader reader = null;
    int count = 0;

    try
    {
        string itemFilter = DoFilter(filter);

        sql = "SELECT v.[Code] FROM [CAS].[dbo].[Van] v WHERE v.[Created_DateTime]>='"   DateStart   "' and b.[Created_DateTime]<='"   DateEnd   "';

        readCommand = new SqlCommand(sql, connection);
        reader = readCommand.ExecuteReader();

        while (reader.Read())
        {
            count  ;
            AddRowItem(sb, reader, count);
        }

        reader.Close(); 
    }
    catch (Exception e)
    {
       //
    }
    return count;
}

The website I to key in the textbox

<div>
      <asp:Label id="Label1" runat="server" Width="100px">Date From:</asp:Label>
      <asp:TextBox id="txtDateS" runat="server" Width="184px"></asp:TextBox><br />
      <br/>
      <asp:Label id="Label2" runat="server" Width="100px">Creation date from:</asp:Label>
      <asp:TextBox id="txtDateE" runat="server" Width="184px"></asp:TextBox><br />
  
      <asp:Button id="cmdRun" runat="server" Width="91px" Height="24px" Text="Run" onclick="cmdRun_Click"></asp:Button>
      <asp:Label id="lblStatus" runat="server"></asp:Label>
      <hr noshade>
</div>

Result output

To further clarify, the Created_DateTime can have null values. So when I filter by the Created_DateTime using the textboxes, it only shows the values where Created_DateTime is not null. E.g. for the start date, I key in 2021-01-30, and end date I key in 2021-12-30. It only shows the rows with a Created_DateTime, but not when there is a null value Created_DateTime.

CodePudding user response：

Based on your comments, it seems you always want to return records with a NULL Created_DateTime. In which case this is how you do it.

SELECT v.Code
FROM dbo.Van v
WHERE (
    v.Created_DateTime >= @DateStart
    -- When dealing with dates that are stored as datetime and could therefore have an accidental time component
    -- its safer to use less than a day in the future
    AND v.Created_DateTime < DATEADD(DAY, 1, CONVERT(DATE,@DateEnd))
)
OR v.Created_DateTime IS NULL;

Note I am showing you the query you should be using which uses parameters rather than string concatenation. Because string concatenation leaves you open to SQL Injection.