Check if datetime is in current date

You need to convert the date in specific format. Because when you will store the date in sql server in datetime data type then sql server automatically set the date with the default time. Now when you make query with getdate() then it will take the current date with the time, so this will not match with the date which you stored with default time.

So you can do this below 2 ways and get the actual result.

1) Convert using Date DataType which is already done above.

2) Convert with the varchar data type with specific format.

select * from X where Convert(varchar(10),Y,120) = CONVERT(varchar(10),GETDATE(),120)

Try this:

WHERE CONVERT(DATE, Y) = CONVERT(DATE, getdate())