Select data from sql where date between range of two textbox

Question

I am trying to select data from sql date where date between range of two textbox with datepicketer,

the problem is it doesn't filter by the date right,

for example the dates in sql are in 28-10-2020, if from date textbox = 28-10-2020 and to date textbox = 25-11-2020 , it will not show any data, but if from date textbox = 28-10-2020 and to date textbox = 30-11-2020 , it will show the data

I don't why this is happening, although the two textbox date and sql date have the same date format (dd-MM-YYYY)

any help will be appreciated.

if (TextBox_from_date.Text != String.Empty && TextBox_to_date.Text != String.Empty)
{
    command += " AND FORMAT(requests_table.[req_apply_time],'dd-MM-yyyy') BETWEEN '" + TextBox_from_date.Text + "' AND '" + TextBox_to_date.Text + "' ";
}

datepicker for textbox:

<script type="text/javascript">
    $(document).ready(function () {
        $(function () {
            $("#TextBox_from_date").datepicker({
                dateFormat: 'dd-MM-YYYY'
            });
        });
    });
</script>

<script type="text/javascript">
$(document).ready(function () {
    $(function () {
        $("#TextBox_to_date").datepicker({
            dateFormat: 'dd-mm-yy'
        });
    });
});
</script>

score 0 · Answer 1 · edited Nov 27 '20 at 06:20

Is it okay that your time pickers have different formats?

Probably you need to add 24 hour to picked date to compare right.

Also it is necessary if this property contains time.

Take a look here

if (TextBox_from_date.Text != String.Empty && TextBox_to_date.Text != String.Empty)
{
    DateTime dateTo = DateTime.Parse(TextBox_to_date.Text, CultureInfo.InvariantCulture);
    dateTo = dateTo.AddDays(1);
    command += " AND FORMAT(requests_table.[req_apply_time],'dd-MM-yyyy') BETWEEN '" + TextBox_from_date.Text + "' AND '" + dateTo.ToString(CultureInfo.InvariantCulture) + "' ";
}

Please align pickers to same format.

<script type="text/javascript">
    $(document).ready(function () {
        $(function () {
            $("#TextBox_to_date").datepicker({
                dateFormat: 'dd-MM-YYYY'
            });
        });
    });
</script>

thanks for the answer I tried the code but I got this message when I ran it "String" was not recognized as a valid DateTime. — arater 2000, Nov 12 '20 at 13:10

score 0 · Accepted Answer · answered Nov 12 '20 at 13:53

after many searching and trying I found the solution:

        if (TextBox_from_date.Text != String.Empty && TextBox_to_date.Text != String.Empty)
        {


            command += " AND requests_table.[req_apply_time]  BETWEEN convert(datetime,'" + TextBox_from_date.Text + "',105)  AND convert(datetime,'" + TextBox_to_date.Text + "',105)";
        }

Select data from sql where date between range of two textbox

2 Answers2