How to specify a wildcard character for date parameters (Views: 29)
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. |