Finding Values That Match a Pattern

Use the like keyword to look for dates that match a particular pattern. If you use the equality operator (=) to search date or time values for a particular month, day, and year, the SAP ASE server returns only those values for which the time is precisely 12:00:00:000AM.

For example, if you insert the value “9:20” into a column named arrival_time, the SAP ASE server converts the entry into “Jan 1 1900 9:20AM.” If you look for this entry using the equality operator, it is not found:

where arrival_time = "9:20" /* does not match */

You can find the entry using the like operator:

where arrival_time like "%9:20%"

When using like, the SAP ASE server first converts the dates to datetime or date format and then to varchar. The display format consists of the 3-character month in the current language, 2 characters for the day, 4 characters for the year, the time in hours and minutes, and “AM” or “PM.”

When searching with like, you cannot use the wide variety of input formats that are available for entering the date portion of datetime, smalldatetime, bigdatetime, bigtime, date, and time values. You cannot search for seconds or milliseconds with like and match a pattern, unless you are also using style 9 or 109 and the convert function.

If you are using like, and the day of the month is a number between 1 and 9, insert 2 spaces between the month and the day to match the varchar conversion of the datetime value. Similarly, if the hour is less than 10, the conversion places 2 spaces between the year and the hour. The following clause with 1 space between “May” and “2”) finds all dates from May 20 through May 29, but not May 2:

like "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.