You can use the like keyword and wildcard characters with datetime, smalldatetime, bigdatetime, bigtime, date, and time data, as well as with char, unichar, nchar, varchar, univarchar, nvarchar, text, and unitext. When you use like with date and time values, Adaptive Server first converts the dates to the standard date/time format, then converts them to varchar or univarchar. Since the standard display formats for datetime and smalldatetime do 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.
Use like when you search for datetime, bigtime, bigdatetime or smalldatetime values, because these types of data 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 applies to 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”, finds all dates from May 20 through May 29, but not May 2. You need not insert the extra space with other date comparisons, only with like, since the datetime values are converted to varchar only for the like comparison.
