Author: Tomas Rutkauskas
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
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.