You can use the like keyword and wildcard characters with datetime, smalldatetime, date and time data as well as with char, unichar, nchar, varchar, univarchar, nvarchar, and text. When you use like with date and time values, Adaptive Server first converts the dates to the standard date/time format and then converts them to varchar or univarchar. Since the standard display formats for datetime and smalldatetime 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 milliseconds.
It is a good idea to use like when you search for datetime or 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%"
This holds true when using date and time datatypes as well.
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.
