Passing a wildcard character to SQL Server

  • Thread starter Thread starter mklapp
  • Start date Start date
M

mklapp

Hello,
Using ADO.NET, I am invoking a SQL Adapter's fill
method. One of the parameters is a date. Most of the
time, the date will be an empty string. The Web Service
method always appends a '%' to the end of the string, so
that most of the time, a "%" is used as the
(smallDatetime) parameter. SQL/SOAP complains that the
string does not look like a date. Well, no it doesn't,
but that is the point of the 'LIKE' phrase, no?

What will it take as a wildcard parameter?

mklapp
 
.. You can use the like keyword and wildcard characters with datetime and
smalldatetime data.
When you use like with datetime or smalldatetime values, Adaptive Server
first converts the dates to the standard datetime format and then converts
them to varchar or univarchar. Since the standard display format does not
include seconds or milliseconds, you cannot search for seconds or
milliseconds with like and a match pattern. Use the type conversion
function, convert, to search for seconds and millisecondsor smalldatetime
values, because datetime or smalldatetime entries may contain a variety of
date parts. For example, if you insert the value "9:20" into a column named
arrival_time, the following clause would not find it because Adaptive Server
converts the entry to "Jan 1, 1900 9:20AM":
where arrival_time = "9:20"
However, this clause would find it:

where arrival_time like "%9:20%"
If you are using like, and the day of the month is less than 10, you must
insert two spaces between the month and day to match the varchar conversion
of the datetime value. Similarly, if the hour is less than 10, the
conversion places two spaces between the year and the hour. The clause like
May 2%, with one space between "May" and "2", will find all dates from May
20 through May 29, but not May 2. You do not need to insert the extra space
with other date comparisons, only with like, since the datetime values are
converted to varchar only for the like comparison.

Let me know is this helped.
 
Hello,

The column is smalldatetime. My intent is for any user
entered date, time excluded, to be concatenated with % to
produce results for that date.
My initial tests were with an empty string (""). The
intent here is for the entire parameter value to consist
of the single wildcard character '%'.
An exception is returned indicating the parameter
value did not look like a date.
The LIKE clause is just a LIKE clause.

In the Enterprise Manager Query grid, anything, even %
2003% raised an exception. %2003& returned data
nonetheless but, '9%' and '11/5/2003%' did not even
though there 'should' have been results. I also used '#'
around the value but that seems to be just an Access
convention.
 
Hi mklapp,

Based on my understanding, you need to convert any number the user entered
to a valid date format. Please point me out if there's any
misunderstanding. However, the SQL server cannot convert it directly. I
think to achieve this, you have to write a function to convert the user
entered value to a valid date format, and then pass it to the SQL.

Merry Christmas and Happy New Year.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hello,

It's not just converting a date, or so I thought.
Let's say the available date component is a month. The
entire dates are entered and displayed in a mm/dd/yyyy
format. If we want all from feb, the intent would have
been :

SELECT * FROM table_name WHERE Date LIKE '02%'

Is the above parameter not valid if the DB column is
smalldatetime? How much of the date is required to use
the LIKE statement? The SQL Server Books Online suggests
the above may work as it demonstrates with something
like :

SELECT * FROM table_name WHERE Date LIKE '%2003%'

The above returns results in Enterprise manager's Open
By Query grid, but also generates an exception. The top
example produces the same exception, but no result rows.

Thanks and I hope you have happy holidays.

mklapp
 
Hi mklapp,

We can query the year with LIKE comparison because the year value contains
4 characters. However the month value is only 2 digits. It might not return
the accurate result set with '%02%' if you want to query all the Feb
values. ('02%' will not work because the storage of date type is not like
what we see on the screen.) Please try to use the greater than (>) or less
than (<) comparison to query the records within a month.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hello,
There are ways to hone the results (e.g. %02/%/%).
That I can handle. My problem is that I do not know how
much of the expression is needed for SQL Server to
recognize the parameter as a date.
I understand the visual representation does not match
the internal format, but isn't that what the query parser
is for? I have used similar logic in Oracle, DB2 and
Access over the years and while my initial results do not
always match what is desired, I can usually make
something good happen.
If the parameter is not accepted because it does not
look like a date then it seems as though LIKE cannot be
used with a date (unless four digits are specified) ? In
spite of the fact the results may not be what one wants,
I would think a parameter of %:% could be successfully
parsed and executed.

Regardless, I will modify the code as you suggest. If
you could, though, could you pass along the requirements
for the parser recognizing that an argument is a date ?
This is something that would be helpful to know.

Thank you,

mklapp
 
Hi mklapp,

Thank you very much for your feedback. I'll send your suggestion through an
appropriate channel.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top