SQL Query for DateTime

Question

I am creating a game where controls need to be disabled if the the gameweek's DateTime associated with those controls are 1 hour or longer away from the current time.

I need to create a query in SQL Server 2008 that will bring back all of the gameweekID's that are 1 hour or earlier than the current time.

So an example will be:

GameweekID 1 - FixtureTime 31/03/2013 10:00 (Stored as DateTime in the database)

So if the current time and date is less than 31/03/2013 9:00 it will appear in my query.

My current attempt is:

SELECT * FROM Fixture WHERE fixtureDate > '2013/03/31 9:00'

But this obviously does not take into consideration the 1 hour before.

I hope this makes sense, was quite confusing to write !

Answer 1

This will give you in the past hour:

MySQL

SELECT columnList 
FROM fixture
WHERE fixtureDate BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()

SQL Server

SELECT columnList
FROM fixture
WHERE fixtureDate BETWEEN DATEADD(month, 1, GETDATE()) AND GETDATE()

This will give you anything greater than 1 hour:

MySQL

SELECT columnList 
FROM fixture
WHERE fixtureDate < DATE_SUB(NOW(), INTERVAL 1 HOUR)

SQL Server

SELECT columnList
FROM fixture
WHERE fixtureDate < DATEADD(month, 1, GETDATE())

Reference

• SQL Server
• MySQL

Answer 2

If you are using SQL Server you would use DATEDIFF

SELECT * FROM Fixture WHERE DATEDIFF(hour, fixtureDate, GetDate()) >= 1