CODEWITHSUNDEEP
mysqlwinforms
Rudra
Rudra

Reputation: 13

How to get record from database between two dates and time?

I am taking date request from the server in the UI:

request.date_from = dateTimePickerFromDate.Value; //7/31/2017 12:33:18 PM request.date_to = dateTimePickerToDate.Value; //7/31/2017 12:33:18 PM

Data is not getting displayed as the time is same for date_from and date_to

And in the database I have the SQL query:

@date_from datetime, @date_to datetime

select Transaction_date from Transaction where (Transaction_Date >= @date_from and Transaction_Date <= @date_to) order by Transaction_date desc

I am getting data if I choose different date, but if I select same date, data is not getting displayed although I have records in the database because of same time.

As I want to display records between the date 7/31/2017 00:00:00 and 7/31/2017 23:59:59 What changes do I need to make either in the SQL query or else in winforms UI ?

Upvotes: 0

Views: 397

Answers (2)

Ben
Ben

Reputation: 763

Assuming you have two DatePicker controls on your form. I would add one day to dt_to.

var dt_from = dateTimePicker1.Value;
var dt_to = dateTimePicker2.Value;
if (dt_from.Date == dt_to.Date)
    dt_to = dt_to.AddDays(1);

[Update]

request.date_from = dateTimePickerFromDate.Value; 
request.date_to = dateTimePickerToDate.Value;
if (request.date_from.Value.Date == request.date_to.Value.Date)
    request.date_to = dt_to.Value.AddDays(1);

[Update 2]

request.date_from = dateTimePickerFromDate.Value; 
request.date_to = dateTimePickerToDate.Value;
if (request.date_from.Value.Date == request.date_to.Value.Date)
    request.date_from = request.date_from.Value.Date;

Upvotes: 0

Priyanka Sankhala
Priyanka Sankhala

Reputation: 868

Use Between in query

select * from table where {date_filed} between {start_data} and {end_date}

Or you can use

select * from table where {date_filed} >= {start_data} and {date_filed} <= {end_date}

but in both date date field need to datetime or timestamp datatype otherwise use str_to_date its use in type case in mysql query

Upvotes: 1

Related Questions