PopSQL

How to Query Date and Time in SQL Server using Datetime and Timestamp

Get the date and time right now (where SQL Server is running):

select current_timestamp; -- date and time, standard ANSI SQL so compatible across DBs
select getdate();  -- date and time, specific to SQL Server
select getutcdate(); -- returns UTC timestamp
select sysdatetime(); -- returns 7 digits of precision

Find rows between two dates or timestamps:

select  *
from events
where event_date between '2020-01-01' and '2020-01-31';

-- can include time by specifying in YYYY-MM-DD hh:mm:ss format:
select  *
from events
where event_date between '2020-01-01 12:00:00' and '2020-01-01 23:30:00';

Find rows created within the last week:

select *
from events
where event_date > (select dateadd(week, -1, getdate()));

Find events scheduled between one week ago and 3 days from now:

select *
from events
where event_date between (select dateadd(week, -1, getdate())) and (select dateadd(day, +3, getdate()));

Extracting part of a timestamp and returning an integer:

select day(getdate()); -- or month() or year()
select datepart(day, getdate()); -- or hour, week, month, quarter, year

Extracting part of a timestamp and returning a string (e.g. "February" or "Monday"):

select datename(month, getdate()); -- or day

Get the day of the week from a timestamp:

select datepart(weekday, getdate()); -- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday

How to convert a timestamp to a unix timestamp:

-- all SQL Server versions: output example - 1580718382
select datediff(second, '1970-01-01' , getutcdate())

-- SQL Server 2016 and later: output example - 2147483648
select datediff_big(second, '1970-01-01' , '2038-01-19 03:14:08')

To calculate the difference between two timestamps, convert them to unix timestamps then subtract:

select datediff(second, '1970-01-01' , timestamp2) - datediff(second, '1970-01-01' , timestamp1) -- output in seconds

Note: this simple approach lacks millisecond precision.

database icon
Shared queries and folders ✅ Version history ✅ One-click connection to SQL Server ✅
Get more done, together, with PopSQL and SQL Server