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
From SQL Server query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and SQL Server