How to Round Timestamps in SQL Server

Rounding or truncating timestamps are especially useful when you're grouping by time. If you are rounding by year, you can use the corresponding function:

select year(getdate()) as 'Year';

Be careful if you are grouping by months or smaller timestamp fragments (weeks, days, hours, minutes). Using the month() function will, for example, make January 2020 and January 2019 both just translate to 1. If that is what you want, then you can use month().

However, if you want to distinguish between months of different years, you need to use format() function:

select format(getdate(),'MM-yyyy'); -- round to month
select format(getdate(),'dd-MM-yyyy'); -- round to day
select format(getdate(),'dd-MM-yyyy hh'); -- round to hour
select format(getdate(),'dd-MM-yyyy hh:mm'); -- round to minute
select format(getdate(),'dd-MM-yyyy hh:mm:ss'); -- round to second
database icon
Shared queries and folders ✅ Version history ✅ One-click connection to SQL Server ✅
Get more done, together, with PopSQL and SQL Server