Handle EndDateTime 23:59

Farhan

hi All,
Just to start with by SQL pulling the data using date range
when I declare and passing the variable through SQL-Server I can get the activity for end date if after noon 00:00 by passing following argument

--DECLARE @startdatetime DATETIME = '01-Sep-2017 23:59', @enddatetime DATETIME = '26-Sep-2017 23:59' 
Select ......
from table
where 
AND ConfirmedDate  Between @startdatetime And @enddatetime

Problem
when I pass the value through Parameter allow user through Date Picker1 /Picker2
Create two parameters
startdatetime (DateTime)
enddatetime (DateTime)

wrote:

Also create the Define Parameter after ADD the SQL as below
Query|Edit|Next|Define Paramter
startdatetime
Name=startdatetime,
DataType=DateTime
Default Value =01/01/2016 00:00
Expression = [startdatetime]
Size = 0
enddatetime
Name =endatetime ,
DataType = DateTime
Defualt Value = 01/01/2016 23:59 ,
Expression = [startdatetime]
Size = 0

when I print the parameter values(enddatetime) after choosing today date from datepicker2 the output like
27/09/2017 00:00:00, I should expect 27/09/2017 23:59:00
Because of that I'm loosing some rows
Is this something I can hand through Expression using Datefunction?
appreciate your help
Farhan

ipong

wrote:

I should expect 27/09/2017 23:59:00

you should do query in desktop program, before sending data to fastreport

                using (SqlConnection cnn = new SqlConnection(connectionString))
                {
                    cnn.Open();

                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = cnn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "sp_yourquery";
                        cmd.Parameters.AddWithValue("@StartDate", date1);
                        cmd.Parameters.AddWithValue("@EndDate", date2);
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            using (DataTable dt = new DataTable())
                            {
                                dt.Load(dr);
                                dt.TableName = "MainReport";

                                FastReport.Report report = new FastReport.Report();
                                report.Preview = PreviewControl1;
                                report.FileName = "ReportName";
                                report.RegisterData(dt, dt.TableName);
                                report.Prepare();
                                report.ShowPrepared();
                            }
                        }
                    }
                }
                PreviewControl1.ZoomPageWidth();

Farhan

Thanks for the response.
sorry I should've mentioned earlier I used vb code so after spending hours found the problem
On Button Click I've used the following code

    Private Sub btnOk_Click(ByVal sender As object, ByVal e As EventArgs)
      
    Dim enddatetime As DateTime = #3/12/1999#
    enddatetime = FormatDateTime (DateTimePicker2.Value & " 23:59")
    Report.SetParameterValue("enddatetime",enddatetime)
    MsgBox(enddatetime)

   End sub

Also the DatePicker2(enddatetime) under DataColumn property I was calling Parameter[enddatetime] I just take it off and left blank
Secondly, I also took off the timestamp default value and leave it like '27/09/2017' instead of 27/09/2017 23:59
And Its work , the parameter value showing like 27/09/2017 23:59:59 now on my page
Thanks again for your help
Kind regards,
Farhan