CODEWITHSUNDEEP
sqlvb.netdatetime
Tohny.Johnson
Tohny.Johnson

Reputation: 67

VB.net SQL Datepicker select date from dropdown but manually specify time

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)

Upvotes: 0

Views: 124

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

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

Upvotes: 2

Related Questions