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 !

Kermit · Accepted Answer · 2013-03-31 16:54:17Z

2

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

edited Mar 31, 2013 at 16:54

answered Mar 31, 2013 at 16:49

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JackofAll Over a year ago

This didn't work. I get an error saying Date_Sub and Now are not recognized functions

Kermit Over a year ago

Well what DBMS are you using?

JackofAll Over a year ago

Sorry I thought I put that in the post, I will edit it. SQL Server

BellevueBob Over a year ago

@PolishPrince +1, nice; you might add this for a "pure ANSI" option: where fixtureDate < (current_timestamp + INTERVAL '1' HOUR)

BellevueBob Over a year ago

@PolishPrince All databases I work on support current_date and current_timestamp as constants (particularly Teradata, Oracle, and SQL Server); they are ANSI standards and make system calls to get the current date and timestamp.

Phil · Accepted Answer · 2013-03-31 16:52:07Z

1

If you are using SQL Server you would use DATEDIFF

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

answered Mar 31, 2013 at 16:52

Phil

43.2k9 gold badges102 silver badges102 bronze badges

Collectives™ on Stack Overflow

SQL Query for DateTime

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related