CODEWITHSUNDEEP
sqlsql-servert-sql
Adam Chetnik
Adam Chetnik

Reputation: 2016

Compare Date/Time in SQL Server T-SQL

We have this format Date/Time stored in a varChar column for each row in our table: 2013-05-26 20:22:07.2894.

How can we use this column in a T_SQL WHERE statement to retrieve the last 1 hour's worth of rows?

We have tried this and it works: WHERE Time_Stamp > '2013-05-26 18:00:00:0000' however we would like to have the T-SQL to work automatically rather than having the date/tie hard coded.

Upvotes: 3

Views: 5845

Answers (4)

Euro Micelli
Euro Micelli

Reputation: 33998

Datediff(hour, time_stamp, getdate()) < 1

But you are likely making a mistake in storing that timestamp as a varchar. When you store it as a varchar, SQL is going to have to check every candidate row on the table to find the records, and it will have to convert every single value of timestamp to a dateline, in order to be able to identify the records you are looking for. Hopefully your query's conditions are such that this will only require checking a handful of records.

But really, there is no reason to store the dates as strings. storing them as datetime takes less space, results in faster queries, and might allows SQL to pick better query plans. hiding the nature of your values from the server only prevents it from helping you out, and causes you to have to worry about what happens when a field value is 'Hello' instead of '2013-05-26 18:00:00:0000' You should store your dates as Datetime, and you should build an appropriate index for it (the details of the index that you need require a lot more information about your system than what you hae provided).

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

To convert your VARCHAR to a DATETIME, just use;

CONVERT(DATETIME, SUBSTRING(myDate, 1, 23), 121);

...where myDate is your column name. Once it's converted to a datetime, a comparison with GETDATE() is simple.

A very simple SQLfiddle.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is a sargable approach (meaning it will use the index):

where Time_Stamp > convert(varchar(255), getdate() - 1.0/24, 121)

Upvotes: 4

Andomar
Andomar

Reputation: 238048

You could combine getdate() with dateadd() to subtract 1 hour from the current time:

WHERE Time_Stamp > dateadd(hour, -1, getdate())

The varchar will be converted to datetime for the comparison because datetime has higher precedence.

Upvotes: 0

Related Questions