0

I have two datefield drop downs that the end-user will select their start and end dates from. However, when I run the query I'm missing the first date, i.e - I select the 9th-15th but I get the 10th-15th records.

My thinking was to somehow get just the day from the dropdown menu and manually hard code the start and end times (00:00:00 and 24:00:00)

I think I might be able to do it similar to this, but my code is not working.

Private Sub FindItem2()
    SQL2.AddParam("@datestart", dateStart.Value.ToShortDateString + '00:00:00')
    SQL2.AddParam("@dateend", dateEnd.Value.ToShortDateString + '24:00:00')
    LoadGrid("SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), LEN(CustomerNumber)),'D','CAFET','', sum(Total) as totalsales FROM ViewDetailedSalesReport WHERE CustomerNumber is not null AND DateSold BETWEEN (@datestart) AND (@dateend) GROUP BY CustomerNumber, CustomerLastName ORDER BY CustomerLastName ASC; ")
End Sub

EDIT NEW:

I deleted my code rather than trying to modify it. I used Cetin's code and tweaked it by moving the variables to the top, removing the tags, and adding quotes around the Query. After that it works, and exactly as it should.

Private Sub FindItem2()
    SQL2.AddParam("@datestart", dateStart2.Value.Date)
    SQL2.AddParam("@dateend", dateEnd2.Value.Date.AddDays(1))
    Dim cmd As String = "SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), 
    LEN(CustomerNumber)),
    'D','CAFET','', 
    sum(Total) as totalsales 
  FROM ViewDetailedSalesReport 
  WHERE CustomerNumber is not null AND 
        DateSold >= @datestart AND DateSold <@dateend
  GROUP BY CustomerNumber, CustomerLastName 
  ORDER BY CustomerLastName ASC;"
    LoadGrid(cmd)
End Sub

EDIT-OLD: I got the query to work "correctly" however I'm not sure this is the best practice way to accomplish this task.

Private Sub FindItem2()
    SQL2.AddParam("@datestart", dateStart2.Value)
    SQL2.AddParam("@dateend", dateEnd2.Value)
    LoadGrid("SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), LEN(CustomerNumber)),'D','CAFET','', sum(Total) as totalsales FROM ViewDetailedSalesReport WHERE DateSold >= DateAdd(day,-1,@datestart) AND DateSold < (@dateend) GROUP BY CustomerNumber, CustomerLastName ORDER BY CustomerLastName ASC; ")
End Sub

I modified the section as suggested by Cetin using >= and < rather than BETWEEN, and changed the dayoffset by -1 using DateAdd.

DateSold >= DateAdd(day,-1,@datestart) AND DateSold < (@dateend)
Improve this question
6
  • A 24-hour clock never reads "24:00:00", because that's the same as "00:00:00". You might want to use "23:59:59" as your "end time" instead. Commented Feb 25, 2020 at 0:37
  • 1
    Your modification do not match mine. Please drop the time part all together and do NOT convert to a string. Simply add the parameters as DateTime values (where time parts are dropped using dtPicker.Value.Date. Commented Feb 25, 2020 at 0:40
  • What datatype is ViewDetailedSalesReport.DateSold? If it's a DATETIME, do the values in the table actually have a time, or are the times all zero'd out? Same questions apply to the datestart and dateend variables in your script. Are they DATES, DATETIMEs or DATETIMEs with zero'd times? Commented Feb 25, 2020 at 0:47
  • @ digital.aaron - They are a date without time. They show as YYYYMMDD in the raw export. @Cetin Bosoz - Removed my code completely, and modified yours to work in environment. I got it working correctly as it should. I edited the OP again to reflect the changes. I just can't wrap my head around why my original code was cutting off a day, and why changing it to >= and < didn't include those days. Commented Feb 25, 2020 at 0:56
  • Convert all your datetimes to dates for comparisons, then. It will greatly simplify things. Also, if came up with a solution that works for you, you can post it as an answer to your own question. Commented Feb 25, 2020 at 0:59

1 Answer 1

Reset to default
2

That is an error trying to convert the DateTime value to a string and also use a DateTime range checking in MS SQL Server using Between. Between is like saying x >= y and x <= z, in other words it is y and z inclusive. Instead it should be a query saying x >= y and x < z (where y and z are start and end dates).

Private Sub FindItem2()
   dim cmd as string = <sql>SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), 
    LEN(CustomerNumber)),
    'D','CAFET','', 
    sum(Total) as totalsales 
  FROM ViewDetailedSalesReport 
  WHERE CustomerNumber is not null AND 
        DateSold >= @datestart AND DateSold < @dateend
  GROUP BY CustomerNumber, CustomerLastName 
  ORDER BY CustomerLastName ASC;
       </sql>
        SQL2.AddParam("@datestart", dateStart.Value.Date)
        SQL2.AddParam("@dateend", dateEnd.Value.Date.AddDays(1))
        LoadGrid(cmd)
End Sub

I assume in your sample code .AddParam is doing something like:

sqlCommand.Parameters.Add("@dateStart", SqlDbType.Date).Value = dateStart.Value.Date

and loadgrid is something like dataTable.Load(sqlCommand.ExecuteReader).

EDIT: Not sure about what those SQL2.AddParam and LoadGrid. Here is a sample:

Dim sql As String = "SELECT SUBSTRING(CustomerNumber, PATINDEX('%[^0]%', CustomerNumber+'.'), 
LEN(CustomerNumber)),
'D','CAFET','', 
sum(Total) as totalsales 
FROM ViewDetailedSalesReport 
WHERE CustomerNumber is not null AND 
    DateSold >= @datestart AND DateSold <@dateend
GROUP BY CustomerNumber, CustomerLastName 
ORDER BY CustomerLastName ASC;"

Dim tbl As DataTable = New DataTable()

Using cn As SqlConnection = New SqlConnection("server=.\SQLExpress;Database=YourDb;Trusted_Connection=yes")
    Using cmd As SqlCommand = New SqlCommand(sql, cn)
        cmd.Parameters.Add("@datestart", SqlDbType.Date).Value = dateStart.Value.Date
        cmd.Parameters.Add("@dateend", SqlDbType.Date).Value = dateEnd.Value.Date.AddDays(1)
        cn.Open()
        tbl.Load(cmd.ExecuteReader())
        cn.Close()
    End Using
End Using

' Do whatever with datatable
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the reply! I took your code, and it does run, but I'm getting a double day offset now. I'm missing the first day, it appears to pull the following day, and now I'm getting an extra day at the end. i.e - 5th - 10th is pulling 6th - 11th. Any ideas? The .AddParam is just a safeguard for writing the SQL query. Rather than passing a direct value into the SQL query I first set a parameter and pass that into the query. And yes the load grid just displays my query into a data table.
I have updated my OP with a "solution" though I'm not sure its best practice to do it the way I did. I'm still not sure how TIME is determined via the DateAndTime Picker as the only thing show are the days, not time.
@Tohny.Johnson, I don't understand what do you mean by 5th-10th is pulling 6th-11th. If chosen dates are 1/1/2020 and 1/31/2020 (whatever the time parts are), code would pull the entire Jan 2020 data. I wonder if there is something wrong with your SQL2.AddParam. Why not simply use cmd.Parameters.Add().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.