How to specify a wildcard character for date parameters (Views: 100)
I'd like to do something like this:
select * from Person where Surname like '%'
but with DOB instead. Is there a way to specify a wildcard character for date parameters? I keep getting type mismatch errors.
The LIKE predicate can only be used with CHAR (or VARCHAR) type values. To use LIKE with a value of any other data type, you would need to use the SQL function CAST to convert the value to CHAR type. For example, converting a DATE type column to CHAR(10):
WHERE(CAST(DOB as CHAR(10))LIKE "%94")
However, if this is performed on a TIMESTAMP type column, the time portion of the column's value can interfere with this. Convert the column first to DATE and then that to CHAR(10).
WHERE(CAST(CAST(DOB as DATE) as CHAR(10))LIKE "%94")
But SQL provides a function specifically for extracting a single element of a DATE or TIMESTAMP value for making such partial-value comparisons: EXTRACT. The EXTRACT function can be applied to a DATE or TIMESTAMP value to retrieve the year, month, or day portion of the date. For example:
WHERE(EXTRACT(YEAR FROM DOB) = 1994)
Note: all of the above is common to SQL-92. These operations are not specific to local SQL.
<< Back to main page