Quick Tip - Date Math in SQL Server

I often need to add where clauses to SQL statements. Most folks probably do. It allows me to view recent log entries or recently created records.

Rather than hard coding dates, I've been using "Date Math" to make these queries usable over a period of time. It also is faster to type thanks to DataGrips' autocomplete.

I wanted to know the impact of -1 or -.1 which are my most frequently used.

Simply enough, -1 is one day. All my dates are in UTC so getutcdate()-1 is yesterday - exactly 24 hours from the point the query is ran. Thus, -.5 is 12 hours ago and my favorite is getutcdate()-.1 is 3 hours ago.

select getutcdate(), getutcdate()-1, getutcdate()-.5,
    datediff(hh, getutcdate(), getutcdate()-1) as MinusOne,
    datediff(hh, getutcdate(), getutcdate()-.5) as MinusPointFive,
    datediff(hh, getutcdate(), getutcdate()-.1) as MinusPointOne
Example of Date Path in DataGrip & SQL Server
Frank Villasenor

Frank Villasenor

Owner and principal author of this site. Professional Engineering Lead, Software Engineer & Architect working in the Chicagoland area as a consultant. Cert: AWS DevOps Pro
Chicago, IL