Get filter the date rand in SQL Server

Question

I have a data table with MobileNo and RequestTime, there are two rows with mobile no and date. I want to get all record with current date

SELECT * 
FROM Requester 
WHERE (Convert(datetime, RequestTime, 103)) = GETDATE()

I have the datetime as a string, so I need to convert it to DateTime in SQL but still the result is null

Have a look the code and result in SQL Server

Answer 1

The DateTime style you chose for the conversion is correct however this is not working because GetDate() gives you also the current time while your stored dates doesn't. Hence the equal cannot be true.

You need to truncate the time of your comparison date to make it works.

Answer 2

Please try this:

SELECT REQUESTERMOBILE,REQUESTTIME
FROM REQUESTER
WHERE CONVERT(DATE,CONVERT(DATETIME,REQUESTTIME),103) = CONVERT(DATE,GETDATE(),103)

Answer 3

SELECT CONVERT(DATETIME, '21/10/2015', 103) return: 2015-10-20 00:00:000.000

SELECT GETDATE() return: 2015-10-20 16:42:01.154 -- get current time

You can try your code like this:

WHERE CONVERT(DATETIME, RequestTime, 103) = CONVERT(DATETIME, CAST(GETDATE() AS DATE), 103)