If I understand your situation correctly I would recommend not using a character column for storing dates. Instead it should be defined as a
date
column.
When the column is a date column, it's easy to extract the day or the month. For example
SELECT *
FROM UserProfile
WHERE DATEPART(day, BirthDay) = 23
AND DATEPART(month, BirthDay) = 8
But the key is to use proper data type for the column.
If you really can't change the data type, then you can use the LIKE example in solution 1 or use CONVERT function to convert the type from varchar to date. If the date format you use is YYYY/MM/DD then something like the following should work
SELECT *
FROM UserProfile
WHERE DATEPART(day, CONVERT(date, BirthDay, 111)) = 23
AND DATEPART(month, CONVERT(date, BirthDay, 111)) = 8
But as said, using date column is the proper way to go.