How do I compare two timestamps as dates in SQL Se

问题:

I want to run a query to bring back that occurred on or after a date where the input date is a timestamp and the and the table is a timestamp. The problem i have is right now results are getting excluded based on time so some go missing. Refer to below sample for details

Sample SQL

Select *
from table
where DateTime >= InputDateTime

Sample Table

Row | DateTime
1   | 2015-01-16 23:12:11
2   | 2015-01-15 06:12:24
3   | 2015-01-14 23:12:24
4   | 2015-01-15 23:12:24
5   | 2015-01-12 23:12:24

Expected result if InputDateTime = 2015-01-15 12:13:24

Row | DateTime
1   | 2015-01-16 23:12:11
2   | 2015-01-15 06:12:24
4   | 2015-01-15 23:12:24

回答1:

If you want to consider only the date, then remove the time component:

where datetime >= cast(getdate() as date)

Of courese, you can do this for a variable or column as well:

where datetime >= cast(@inputdatetime as date)

回答2:

Now that I understand that you are wanting to compare based solely off of date, I am changing my code to reflect a way to do so:

DECLARE @inputDateTime DATETIME = '2015-01-15 12:13:24'

-- DROP TABLE [#testTable]
CREATE TABLE [#testTable]
(
[Row] INT IDENTITY(1,1),
[DateTime] DATETIME
)

INSERT INTO [#testTable]
    (
    [DateTime]
    )
VALUES
    ('2015-01-16 23:12:11'),
    ('2015-01-15 06:12:24'),
    ('2015-01-14 23:12:24'),
    ('2015-01-15 23:12:24'),
    ('2015-01-12 23:12:24')

SELECT
    *
FROM
    [#testTable]
WHERE
    [DateTime] >= CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,@inputDateTime)))