Check if datetime is in current date

Question

I am using SQLServer 2008. I have a table X with field Y that is a datetime format. In the WHERE statement of my query I only want to keep the rows where the date of field Y equals the current date.

I have searched the internet but couldnt find an example that works for SQLServer/

Thank you for your help!

Solution 1

Try this:

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

Solution 2

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)