Can I use YEAR(CURDATE()) within a like statement?-CodePudding

In a SQL script I'm trying to get data based on the year within a string value field. Each year the table adds a new row for the current year . So the ID_TYPE_NAME column has a row with:

"MS-DRG V38 (FY 2021)" "MS-DRG V39 (FY 2022)" and so on...

Being new to SQL I hoped that a formula like the one below would work:

WHERE   ID_TYPE_NAME LIKE 'MS-DRG%'
    and ID_TYPE_NAME LIKE YEAR(CURDATE())

but I get a message that

CURDATE is not a recognized built-in function name

Can I use like or do I need to go a different route? If I must go a different route, what method do I use to accomplish this?

CodePudding user response：

There's nothing magical about LIKE arguments, it's just a regular string where certain characters (namely % and _) are handled as wildcard characters. Your error about CURDATE not being recognized as built-in function is because you're calling a function that doesn't exist, just like if you run select pink_elephants().

To compose a string with a pattern that suits your needs you can use these tools:

CURRENT_TIMESTAMP to get current date and time (this is a feature from standard SQL that's available in many dialects)
YEAR() to extract year portion
CAST() to convert year (an integer) to text
to concatenate substrings

All together:

where id_type_name like 'MS-DRG% (FY '   cast(year(current_timestamp) as varchar(4))   ')'

Demo

CodePudding user response：

We can use CONCAT to build the string which should be found, something like this:

SELECT ID_TYPE_NAME
FROM yourtable
WHERE ID_TYPE_NAME 
LIKE CONCAT('MS-DRG%',YEAR(GETDATE()),')');

YEAR takes just the year from the whole current date.

So the LIKE condition will be created as: MS-DRG 22)

Or we can be even more specific and do this:

SELECT ID_TYPE_NAME
FROM yourtable
WHERE ID_TYPE_NAME 
LIKE CONCAT('MS-DRG%FY ',YEAR(GETDATE()),')');

The LIKE condition of this query will be created as: MS-DRG%FY 2022)

Both queries will return only this result from your sample data: MS-DRG V39 (FY 2022)

Try out: db<>fiddle