Using Date Parts

The bread and butter of SQL analysis is grouping by a period of time and then counting the rows for each period. Whether your period is an hour, day, week, or month, Postgres nerds do it with date_trunc:

select date_trunc('week', created_at), count(1) from...

MS SQL Server, speed demon though it is, lacks nice functions for this. In this post, we’ll show you how to truncate datetimes up to seconds, minutes, hours, days, weeks, months, and years in SQL Server.

Seconds, Minutes, Hours: Convert To String and Truncate

To convert to seconds, minutes, or hours we will convert the datetime to a string, truncate it, and convert the resulting string back to a datetime. Here’s the SQL for each:

Second

select convert(
  datetime, 
  convert(varchar(19), created_at, 120)
) 
from...

Minute

select convert(
  datetime, 
  convert(varchar(16), created_at, 120) + ':00'
) 
from...

Hour

select convert(
  datetime, 
  convert(varchar(13), created_at, 120) + ':00:00'
) 
from...

In each query, convert(varchar(19), created_at, 120) converts the created_at datetime to a string. 120 specifies a canonical yyyy-mm-dd hh:mi:ssformat for our string. Then the length of the varchar is the number of characters we want to keep from that string.

For minutes and hours, we add :00 and :00:00 respectively to the end of our string to make a valid datetime. Then we simply convert back to datetime!

Days: Convert to Date

While not quite as nice as the date function in some DBs, SQL Server will let you convert your datetime to a date in one step:

select convert(date, created_at) from...

Here’s what’s happening:

  • datepart(dw, created_at) is the day of week of our created_at
  • dateadd(dd, 1 – datepart(dw, created_at)) subtracts enough days from created_at to pull it back to the first day of the week.

Finally, dateadd may return a full datetime, so we convert up to date at the end.

Months & Years: Convert To String, Truncate, Convert To Date

Month and year work similarly to seconds, minutes, and hours. Here’s the SQL:

Month

select convert(
  date, 
  convert(varchar(7), created_at, 120) + '-01'
)
from...

Year

select convert(
  date, 
  convert(varchar(4), created_at, 120) + '-01-01'
)
from...

Like before, we convert created_at to the string format we like and then truncate. In this case, we either take 4 or 7 characters for years or months, respectively.

Then we append -01 or -01-01 to make a valid date string and convert to a date. Voila!

Tags: