CODEWITHSUNDEEP
sqlsql-server
Some User
Some User

Reputation: 5827

SQL - Subtracting Milliseconds from Current Date Time

I am trying to write a query that will let me get records entered between now and a some number of minutes in the past. I have the number of minutes as milliseconds. So, I'm trying to write a SQL query that will let me get those records.

I see a DateAdd function. However, I don't see a function to Subtract some time from a date. From a pseduo-code perspective, I'm trying to do this:

SELECT
  *
FROM
  MyTable
WHERE
  CreatedAt > (GetUtcDate() - milliseconds)

How do I get records from MyTable that have happened within some past window of time?

Upvotes: 1

Views: 7038

Answers (4)

Eric Brandt
Eric Brandt

Reputation: 8101

Despite its name, the DATEADD function is also the "DATESUBTRACT" function that you're looking for. (Intentionally double-quoted as quasi-code.)

The first parameter defines the sorts of units you're adding or subtracting, i.e. HOUR, DAY, WEEK, etc., or, in your case, MILLISECOND. In the second parameter, if you want to add time, you enter a positive number. If you want to subtract time, as you do here, enter a negative number of units.

Upvotes: 1

Adminorama
Adminorama

Reputation: 46

The best way to interact with date and time in SQL is to use specific date and time functions rather than adding dates as you would add numbers.

In this case the function you are looking for is DATEADD, using it your cord should change like this:

SELECT *
FROM MyTable
WHERE CreatedAt > DATEADD(ms, -milliseconds, GetUtcDate())

Upvotes: 0

Thom A
Thom A

Reputation: 95906

The function you want is DATEADD (Transact-SQL).

SELECT {YourColumns}
FROM MyTable MT
WHERE CreatedAt > DATEADD(millisecond,{value},GETUTCDATE());

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

We can try using DATEADD in millisecond mode:

SELECT *
FROM MyTable
WHERE CreatedAt > DATEADD(ms, -60000, GETDATE());  -- 60000 ms = 1 minute

This would return all records which were created within one minute of the current date and time.

Upvotes: 4

Related Questions