How do I compare two timestamps as dates in SQL Server

Question

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

score 3 · Answer 1 · answered Jan 22 '16 at 00:19

3

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)

answered Jan 22 '16 at 00:19

Gordon Linoff

1,242,037
58
646
786

SQLPhilosopher · Answer 2 · 2016-01-22T00:47:31.200

0

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)))

edited Jan 22 '16 at 00:47

answered Jan 22 '16 at 00:24

SQLPhilosopher

3
2

The 2015-01-15 12:13:24 is supposed to be midday which in is after 6 am e.g. 12:13 PM. Its the same day but would get excluded if it was a time stamp comparison – user1605665 Jan 22 '16 at 00:40
Ah, I see what you mean now, then just truncate your inputDateTime variable at comparison: 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))) – SQLPhilosopher Jan 22 '16 at 00:45

How do I compare two timestamps as dates in SQL Server

2 Answers2

Linked