Datetimepicker Selection just for one day...?

Goran Bibic

Datetimepicker from and datetimepicker to....

Work just for one day exp, 16.03.2018....16.03.2018

Work fine

If choose 01.03.2018 to 16.03.2018 dont work...

What I wrong?

private void radni_sati2()
{
String cs = "Data Source=.\\SQLEXPRESS;Initial Catalog=bss_prijava_radnika;Integrated Security=True";
using (SqlConnection openCon = new SqlConnection(cs))
{ string saveStaff = "SELECT a.IdPrijava, a.redni_broj , a.radnik, isnull(convert(varchar(20), a.vrijemeodjave, 113), '') as vrijemeodjave, convert(varchar(20), b.vrijemeprijave, 113) as vrijemeprijave, "
+ "CASE WHEN (DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) END AS radni_sati, "
+ "CASE WHEN(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) > 8 THEN(DATEDIFF(SECOND, b.vrijemeprijave, a.vrijemeodjave) / 3600) - 8 ELSE NULL END AS prekovremeni "
+ "FROM(SELECT IdPrijava, redni_broj, radnik, vrijemeodjave, ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) as RowNum "
+ "FROM dbo.prijava_radnika) a INNER JOIN(SELECT IdPrijava, redni_broj, radnik, vrijemeprijave, (ROW_Number() OVER(Partition By IdPrijava ORDER BY vrijemeprijave) - 1) as RowNumMinusOne FROM dbo.prijava_radnika) b ON a.IdPrijava = b.IdPrijava where 1 = 1 ";
if (!string.IsNullOrEmpty(textBox1.Text))
{
saveStaff += " AND a.radnik LIKE \'%" + textBox1.Text + "%\' ";
}
if (checkBox1.Checked)
{
String strFilterDTP1 = dateTimePicker1.Value.ToString("yyyy-MM-dd");
String strFilterDTP2 = dateTimePicker2.Value.ToString("yyyy-MM-dd");
saveStaff += " AND b.vrijemeprijave between '" + strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP1 + " 23:59:59' ";
saveStaff += " AND a.vrijemeodjave between '" + strFilterDTP2 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59' ";
}
String someDate = "25 Mar 2018 13:13:00";
DateTime someDT = DateTime.Parse(someDate);
Console.WriteLine(saveStaff);

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Try using the following code first:
saveStaff += " AND b.vrijemeprijave between '" + strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59' ";
saveStaff += " AND a.vrijemeodjave between '" + strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59' ";
If you get expected results then it is okay. Otherwise try following:
saveStaff += " AND (b.vrijemeprijave between '" + strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59' ";
saveStaff += " OR a.vrijemeodjave between '" + strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59') ";
Have a look at the data. Arrive at the conclusion logically. Identify requirement correctly. Very important.
Goran Bibic8y ago
First solution is ok...Thank you
Goran Bibic8y ago
Yes, but we are not good understadn. Sory...I need solution like this
String strFilterDTP1 = dateTimePicker1.Value.ToString("yyyy-MM-dd");
                    String strFilterDTP2 = dateTimePicker2.Value.ToString("yyyy-MM-dd");

                    saveStaff += " AND b.vrijemeprijave between '" + strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP1 + " 23:59:59' ";
                    saveStaff += " AND a.vrijemeodjave between '" + strFilterDTP2 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59' ";
I need solution like this
String strFilterDTP1 = dateTimePicker1.Value.ToString("yyyy-MM-dd");
                    String strFilterDTP2 = dateTimePicker2.Value.ToString("yyyy-MM-dd");

                    saveStaff += " AND b.vrijemeprijave between '" + strFilterDTP1 + " 00:00:00' AND a.vrijemeodjave '" + strFilterDTP2 + " 23:59:59' ";
Nitin Sontakke8y ago
As discussed yesterday, you have ONLY TWO dates. Right? Then you can apply filter to ONLY ONE column.

Have you decided on which column you want the filter to work?

Otherwise we can apply filter to BOTH column with the help of OR.

How do you want it?

For example

and (column1 between dtp1 and dtp2 or column2 between dt1 and dtp2)

Is above acceptable?