[RESOLVED] how to query data in sql base on the date selected by a user in date time picker

hi All,

I want to modify my program, how can i query data to sql server 2008 based on the date inputed by user in date time picker ?

see image:
Attachment 160227

i try code below but throw error : near ',',

Code:

---

  sqlCmd.CommandText = "WITH Src AS (SELECT station_no,CASE WHEN TestResult = 'ok' THEN 1 END AS ok,CASE WHEN TestResult = 'NG' " &
                "THEN 1 END AS NG,CASE WHEN TestResult = 'RR' THEN 1 END AS RR FROM Muscatel_MPCA_FCT1_LogData_T WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "' and Station_No in (1,2,3,4,5,6))SELECT station_no, COUNT(ok) AS TotalOK,COUNT(NG) " &
                "AS TotalNG,COUNT(RR) AS TotalRR,COUNT(ok) + COUNT(NG) + COUNT(RR) AS Total FROM Src GROUP BY ROLLUP (Station_No)ORDER BY " &
                "Station_No DESC"

---

thanks in advance.

Thread moved from the 'CodeBank VB.Net' forum (which is for you to post working code examples, not questions) to the 'VB.Net' forum


Rather than appending values to the SQL statement, it is far safer and more reliable to use Parameters.

For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).


In simple terms you should be changing from this:

Code:

---

  sqlCmd.CommandText = "...WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "' and Station_No ..."

---

...to something like this:

Code:

---

  sqlCmd.CommandText = "...WHERE SQLDateTime >= @MinDate AND SQLDateTime <= @MaxDate and Station_No ..."
  sqlCmd.Parameters.AddWithValue("@MinDate", dtpFrom.Value)
  sqlCmd.Parameters.AddWithValue("@MaxDate", dtpTo.Value)

---

In addition to using parameters you do have an extra comma that needs removed shown in red below

Quote:

---

WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") "'

---

Quote:

---

Originally Posted by si_the_geek

Thread moved from the 'CodeBank VB.Net' forum (which is for you to post working code examples, not questions) to the 'VB.Net' forum


Rather than appending values to the SQL statement, it is far safer and more reliable to use Parameters.

For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).


In simple terms you should be changing from this:

Code:

---

  sqlCmd.CommandText = "...WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "' and Station_No ..."

---

...to something like this:

Code:

---

  sqlCmd.CommandText = "...WHERE SQLDateTime >= @MinDate AND SQLDateTime <= @MaxDate and Station_No ..."
  sqlCmd.Parameters.AddWithValue("@MinDate", dtpFrom.Value)
  sqlCmd.Parameters.AddWithValue("@MaxDate", dtpTo.Value)

---

---

hi Sir,
what if i want to QUERY EXACTLY 1 DAY ? not just date. example CODE below.1 whole day. is there a way to do that ?

Code:

---

WHERE SQLDateTime >= '2018-07-02T00:00:01 AM'
AND SQLDateTime <= '2018-07-02T23:59:59 AM'

---

I did it already using string query as below: is that also possible using parameters and sql server 2008 ?

Code:

---

and TEST_START BETWEEN TO_DATE('" + dtpFrom.Value.ToString("yyyyMMdd") + "','yyyymmdd')and to_date('" + dtpTo.Value.ToString("yyyyMMdd 23:59:59") + "','yyyymmdd hh24:mi:ss')

---

If you care about the date and not the time then use the date and not the time, e.g.

vb.net Code:

Dim startDate = myDateTimePicker.Value.Date
Dim endDate = startDate.AddDays(1)
Dim sql = "SELECT * FROM MyTable WHERE [Date] >= @StartDate AND [Date] < @EndDate"

Take particular note of the comparison operators. If the user selects, for instance, #1/01/2000# then that SQL would be equivalent to:

vb.net Code:

Dim sql = "SELECT * FROM MyTable WHERE [Date] >= '2000-01-01 00:00:00' AND [Date] < '2000-01-02 00:00:00'"

so it's any time from midnight on the morning of the specified date to before midnight on the following morning.

hi Sir,
I care about time and date. example i query from july 12 2018 to july 12 2018, i choose same date from 2 date time picker.
it should return data from morning of July 12 to current time.

and if i query july1 2018 to july 12 2018 thats the time i care about date.
from my query on oracle below it works .

Code:

---

and TEST_START BETWEEN TO_DATE('" + dtpFrom.Value.ToString("yyyyMMdd") + "','yyyymmdd')and to_date('" + dtpTo.Value.ToString("yyyyMMdd 23:59:59") + "','yyyymmdd hh24:mi:ss')

---

but it not use parameter and i think not support in sql server 2008 . please adviced thanks.

You can still use jmcilhinney's suggestion, you just need to set the values like this:

Code:

---

Dim startDate = dtpFrom.Value.Date
Dim endDate = dtpTo.Value.Date.AddDays(1)

---

Quote:

---

Originally Posted by BONITO

I care about time and date. example i query from july 12 2018 to july 12 2018, i choose same date from 2 date time picker.
it should return data from morning of July 12 to current time.

---

By time, jmc was meaning that you are not worried about start or end times in the middle of a day, just results from full days. If you wanted results from 1 "shift" of work (0600-1800 for example) then time would be important.

hi Sir,

I do that but still cannot query from July 13,2018 to July 13,208.

Code:

---


Dim startDate = dtpFrom.Value.Date
Dim endDate = dtpTo.Value.Date.AddDays(1)

'Query
WHERE  SQLDateTime >=  @startDate AND SQLDateTime < @endDate  and

---

I try to query in sql server 2008 using same date it really return nothing . but when i put time to the Date_To it can able to query .
see my code in sql . this one works.

Code:

---

WHERE SQLDateTime >= '2018-07-11'
AND SQLDateTime < '2018-07-11 23:59:59'

---

but this one below , not works

Code:

---

WHERE SQLDateTime >= '2018-07-11'
AND SQLDateTime < '2018-07-11'

---

so i think i need to declare time also for my endTime not just date , so i try code below, but still not work.

Code:

---

  Dim startDate = dtpFrom.Value.Date
        Dim endDate = dtpTo.CustomFormat
        dtpTo.Format = DateTimePickerFormat.Custom
        dtpTo.CustomFormat = "dddd, MMMM dd, yyyy HH:mm:ss"

---

please advice.

No you do not need to declare time. You're really not thinking about this. Look at the code you posted:

Code:

---

WHERE SQLDateTime >= '2018-07-11'
AND SQLDateTime < '2018-07-11'

---

How can any value of SQLDateTime ever possibly be both greater than or equal to a specific date AND less than that same date at the same time? Why do you think that we used AddDays(1) in our code? The whole point is that the end date in the query is one day greater than the last day you want records for. If you want records for just '2018-07-11' then your start date in the query needs to be '2018-07-11' and you end date in the query needs to be '2018-07-12'. That would mean this code:

Code:

---

WHERE SQLDateTime >= '2018-07-11'
AND SQLDateTime < '2018-07-12'

---

When you omit the time your are implicitly specifying '00:00:00' i.e. midnight on the morning of the date specified. That means that that second query is going to get every record where the date part of SQLDateTime is '2018-07-11', regardless of the time.

Going back to your first code snippet:

Code:

---

Dim startDate = dtpFrom.Value.Date
Dim endDate = dtpTo.Value.Date.AddDays(1)

'Query
WHERE  SQLDateTime >=  @startDate AND SQLDateTime < @endDate  and

---

if the user selects July 13, 2018 in both DateTimePickers then 'startDate' will be #7/13/2018# and 'endDate' will be #7/14/2018# so that query will get every record where the date part of SQLDateTime is '2018-07-13', regardless of time. That is it, that is all. I've done this myself many times and so have many other people, probably including all those others who have contributed to this thread. If you do as instructed, it will work as intended. If it doesn't work as intended, you must not have done as instructed.

Hi Sir,

I got it already! thanks a lot !!
I found the problem in my code. it was in the call of my PARAMETER.