datetimepicker query dates in sql server

[CaryRoberts]

I have a form that has two datetimepicker's (From and To) I am trying to select dates from date time field.
If I manually select dates with whereClause = "InspectionDate > '2012-09-04 07:50:41'
AND InspectionDate < '2012-10-04 07:44:04'" it will work. If I pass the value from the date time picker it wants to reformat the date.
I have tried to re-format the date with Dim tDate As String = frmTech1.ToDatePicker.Value.ToString("yyyy-MM-dd") which works but I cannot query table with a string.
My where clause looks like this whereClause = "InspectionDate > " & vFrom & " " & "00:00:00" '& " " & "AND" & " " & "InspectionDate < " & vTo & " " & "00:00:00" & " " & "AND" & " " & "FieldTechnician" = "Tech" .

How do I convert this string format (yyyy-MM-dd) back to a date?


Cary Roberts , GISP
CVMVCD

[CaryRoberts]

This is classic vb string building.  Your messagebox looks right because it concatenates the variables together.

Does this work, add "'" (quote, single quote, quote) around your variables. Maybe it's + instead of &, been a long time since I VB'd anything.

whereClause = "InspectionDate >= " & "'" & fromDate & "'" & " And InspectionDate <= " & "'" & toDate & "'"

That does work. All I was missing was the (quote, single quote, quote) , this is what I tried before your response.
'whereClause = "InspectionDate > " & fromDate & " " & "00:00:00" & " " & "AND" & " " & "InspectionDate < " & toDate & " " & "00:00:00" & " " '" & "AND" & " " & "FieldTechnician" = "Tech"
            'whereClause = "InspectionDate > '2012-09-04 07:50:41' AND InspectionDate < '2012-10-04 07:44:04'" '
            'whereClause = "InspectionDate > " & fromDate & " " & " AND " & " " & "InspectionDate < " & toDate
            'whereClause = "InspectionDate > " & fromDate & " " & "and" & " " & "InspectionDate < " & toDate & ""

            whereClause = "InspectionDate >= " & "'" & fromDate & "'" & " And InspectionDate <= " & "'" & toDate & "'"
            'whereClause = "InspectionDate < '2012-09-04 07:50:41'"
            'whereClause = "InspectionDate > #" & fromDate & "# AND InspectionDate < #" & toDate & "#"
            ' whereClause = "InspectionDate BETWEEN " & fromDate & " And " & toDate & ""
            'whereClause = "InspectionDate > " & fromDate & "00:00:00"  " " & "AND" & " " & "InspectionDate  <  " & toDate & 18:00:00""
            '" & "AND" & " " & "FieldTechnician" = "Tech"""


The flat spot is starting to disappear. Thanks Much lesson learned.

[AlexanderGray · Accepted solution]

I still think the user of string.format would avoid all this concatenation complexity.

[CaryRoberts]

Be new to VB.Net and Visual Studio, I remember when everything was esri.core and alot of the commands seemed much simpler.
I am intrigued by your where clause in a string.format function, I will have to look at that later. Just trying to get up to speed after a bit of a layoff. Trying to figure out what SQL wants, what the VS will give you and the settings on the development box can be a little confusing especially when it comes to dates. Thanks again Mr. Gray