Get filter the date rand in SQL Server

问题:

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

回答1:

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)

回答2:

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.

回答3:

Please try this:

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