How to retrieve records for the last 30 minutes in MS SQL?

sql

date-handling

performance-optimization

best-practices

byNikita Barsukov·Nov 29, 2024

To retrieve records from the last 30 minutes in YourTable, use the following snippet:

SELECT * FROM YourTable
WHERE DateTimeField > DATEADD(MI, -30, GETDATE());

In this case, DATEADD function shaves 30 minutes off the current time retrieved by GETDATE() function, giving you a time threshold. This allows you to access records from the last 30 minutes.

`DATEADD` and `GETDATE`: Your trusty time-travel duo

DATEADD and GETDATE() are your two guardians to ensure proper handling of dates and times in MS SQL. Avoid sloppy practices, like using CURRENT_TIMESTAMP - (1.0/48.0), which can cause inaccurate results or raise eyebrows among fellow DBAs.

Traps and tricks

Always watch your step! The SQL world is full of tricky pitfalls - like confusing MySQL's DATE_SUB with MS SQL's DATEADD. And remember, if you're getting weird results, might be time to give your comparison operators a once-over.

Big dogs need big data

If you're pulling from a large dataset, don't forget to index DateTimeField. It's like finding your keys before leaving home - it makes things a lot faster!

Time zone tumbles

Working across time zones? Make it a habit of using AT TIME ZONE for SQL Server 2016 and newer, or adjust to UTC when necessary.

Extra tips in your toolkit

Use the NOLOCK hint to bypass the bouncer (locking issues!) on popular tables.
Set up a job or script to do the legwork for you if you need this data regularly.
Invalid date formats won't trip you up if you use TRY_CONVERT or TRY_CAST. It's like the SQL equivalent of learning MMA - you can handle anything!