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.

From SQL Server query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and SQL Server

How to Alter Sequence

How to Group by Day, Date, Hour, Month or Year in SQL Server

From SQL Server query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and SQL Server