topic Re: datetimepicker query dates in sql server in ArcObjects SDK Questions

datetimepicker query dates in sql server

CaryRoberts — Thu, 28 Feb 2013 16:25:32 GMT

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

Re: datetimepicker query dates in sql server

LeoDonahue — Thu, 28 Feb 2013 16:33:29 GMT

Look at the API for C# (assuming), for something called DateTime. It has a parse method for converting yyyyMMdd to a date.

VB probably has something similar.

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

Re: datetimepicker query dates in sql server

AlexanderGray — Fri, 01 Mar 2013 17:13:05 GMT

Looks like you are missing a single quote in the concatenation. The way you build the where clause is very difficult to read and somewhat inefficient. Each time you concatenate two strings it declares a new string, so the line bellow declares 13 new strings in memory...
"InspectionDate > " & vFrom & " " & "00:00:00" '& " " & "AND" & " " & "InspectionDate < " & vTo & " " & "00:00:00" & " " & "AND" & " " & "FieldTechnician" = "Tech"

I prefer using the string.format function, in vb.net it looks like this (sorry it has been a little while since I last did C#)
string.format (InspectionDate > '{0} 00:00:00' AND InspectionDate < '{1} 00:00:00' AND FieldTechnician = {2}", vFrom, vTo, Tech)

In my experience it is a lot easy to spot syntax problems in a statement like that than some long series of &.

Another thing, you can pick up the hours, minutes and seconds in the datetime of the picker to string:

ToDatePicker.Value.ToString("yyyy-MM-dd HH:mm:ss")

Makes the where clause a little easier too.

Re: datetimepicker query dates in sql server

CaryRoberts — Fri, 01 Mar 2013 22:35:36 GMT

Sorry I didn't post that I was using VB.Net. Seems like every thing I'v read wants to convert the date to a string, and then when I convert back to date it takes the machine setting. So, when I do a custom format for datetimepicker as yyyy-MM-dd it shows in the form window as 2013-02-15 which is the date that I want to use for the whereclause. I have converted to string, parsed string for each value (year,day,month) and then converted back to date, but it shows as 2/15/2013 arrgg. I tried your link and have read it many times (hunting for cure) but it deals with strings. Must be missing something here. I am new to .net, old vba guy. I have tried globalization and the culture thing, but still no,luck. Can I still query sqlserver date field which is formatted like this yyyy-MM-dd hh:mm:ss. with a date like this mm/dd/yyyy? I could manually select with the values with just the date and not the hms, so I will have to reduce the humongous where clause, never really got there yet because of the date thing.
Sorry for the rambling. Any help is greatly appreciated.

Re: datetimepicker query dates in sql server

AlexanderGray — Mon, 04 Mar 2013 12:31:43 GMT

I am not sure why you want to take a date and convert it to a string and convert it back to a date... Where clauses take strings, sql server will convert it back to a date to do its query internally.

Re: datetimepicker query dates in sql server

CaryRoberts — Mon, 04 Mar 2013 17:04:58 GMT

The only reason was to be in the same format as the date field. yyyy-MM-dd, hey, if I don't have to convert it to string and back to a date field great. I was using toDate = frmTech1.ToDatePicker.Value.ToString("yyyy-MM-dd"). So i shouldn't mess with format and leave it alone and sql server will format it correctly, is that what your saying? So it should be toDate = frmTech1.ToDatePicker.value and the where clause would be what exactly?

Re: datetimepicker query dates in sql server

AlexanderGray — Mon, 04 Mar 2013 17:56:00 GMT

The only reason was to be in the same format as the date field. yyyy-MM-dd, hey, if I don't have to convert it to string and back to a date field great. I was using toDate = frmTech1.ToDatePicker.Value.ToString("yyyy-MM-dd"). So i shouldn't mess with format and leave it alone and sql server will format it correctly, is that what your saying? So it should be toDate = frmTech1.ToDatePicker.value and the where clause would be what exactly?

I don't think that would work... I would think either you will get a type mismatch or the string returned by to the date picker will be whatever your locale is set which may or may not be wrong. There are two issues here, the conversion of the .net datetime type to string and the conversion from string to the sql server datetime (or date, datetime2) format in sql server. The .net tostring function is compiled when the .net code is compiled and executed on the local client at run time. The where clause is then passed as a string to a sql server database (local or on a server) which interprets and executes the sql where clause. In order to go from one the the other, you need a string in between to pass (whereclause.) The format of the string sql server needs will dictate what format the string you need to make from the .net datetime tostring function. Do you know the format the sql server needs to take in? Can you use a t-sql convert function in the where clause specifying the type of string (sql server's way of specifying the format)?

Re: datetimepicker query dates in sql server

CaryRoberts — Mon, 04 Mar 2013 18:02:34 GMT

I don't think that would work... I would think either you will get a type mismatch or the string returned by to the date picker will be whatever your locale is set which may or may not be wrong. There are two issues here, the conversion of the .net datetime type to string and the conversion from string to the sql server datetime (or date, datetime2) format in sql server. The .net tostring function is compiled when the .net code is compiled and executed on the local client at run time. The where clause is then passed as a string to a sql server database (local or on a server) which interprets and executes the sql where clause. In order to go from one the the other, you need a string in between to pass (whereclause.) The format of the string sql server needs will dictate what format the string you need to make from the .net datetime tostring function. Do you know the format the sql server needs to take in? Can you use a t-sql convert function in the where clause specifying the type of string (sql server's way of specifying the format)?

I think I got it. I set the variable to fromDate = frmTech1.FromDatePicker.Value.Date and fromDate is dimmed as Date. I perform the selection where clause as whereClause = "InspectionDate > " & fromDate & "" and it selected the values, all I need to do now is put multiple selections together. I previously just set the variable as value and nor as value.date.

Re: datetimepicker query dates in sql server

CaryRoberts — Tue, 05 Mar 2013 17:23:51 GMT

I think I got it. I set the variable to fromDate = frmTech1.FromDatePicker.Value.Date and fromDate is dimmed as Date. I perform the selection where clause as whereClause = "InspectionDate > " & fromDate & "" and it selected the values, all I need to do now is put multiple selections together. I previously just set the variable as value and nor as value.date.

Im at a standstill here trying to figure out the whereclause. I have two variables toDate and fromDate both strings equal to frmtech1.toDatePicker.Value and frmTech1.fromDatePicker.value. My selection where clause is whereclause = "InspectionDate >= " & fromDate this works. If I try to append to the where clause like this whereClause = "InspectionDate >= " & fromDate & " And InspectionDate <= " & toDate it doesn't work. I put into MsgBox and got this "InspectionDate >= 2/11/2013 And InspectionDate <= 2/22/2013" looks good to me but doesn't work.
I took it apart and tried just less than, whereclause = "InspectionDate <= & fromDate it doesn't work. Which leads me to believe its a problem with the < or <=. I checked the format of the datepicker and both are formatted the same. Sql server values from the
select by attributes box is "InspectionDate < '2012-09-04 07:50:41'" this works and it also works if I choose to use just the date in the same format as the string variable 2/05/2013 without the time. I am befuddled here, I have a flat spot on my head where I have been beating it. Visual Studio 2008 Any help would be greatly appreciated.

Re: datetimepicker query dates in sql server

LeoDonahue — Tue, 05 Mar 2013 17:53:00 GMT

If I try to append to the where clause like this whereClause = "InspectionDate >= " & fromDate & " And InspectionDate <= " & toDate it doesn't work.

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 & "'"

Re: datetimepicker query dates in sql server

CaryRoberts — Tue, 05 Mar 2013 19:13:56 GMT

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.

Re: datetimepicker query dates in sql server

AlexanderGray — Wed, 06 Mar 2013 12:20:06 GMT

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

Re: datetimepicker query dates in sql server

CaryRoberts — Wed, 06 Mar 2013 13:24:58 GMT

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