How to specify a wildcard character for date parameters

Problem/Question/Abstract:

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.

Answer:

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):

SELECT *
FROM Person
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).

SELECT *
FROM Person
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:

SELECT *
FROM Person
WHERE(EXTRACT(YEAR FROM DOB) = 1994)

Note: all of the above is common to SQL-92. These operations are not specific to local SQL.