Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Date Parts in SQL Server

Date Parts in SQL Server
July 2, 2014

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...

Weeks: dateadd and datepart

Week parts might be the weirdest ones of all. Let’s start with the full SQL:

select convert(
  date, 
  dateadd(dd, 1 - datepart(dw, created_at), 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!

An easier way

If you’re a Periscope user, just type select [created_at:week] or select [created_at:year] and we’ll do the crazy conversions for you! We’ll even do them in multiple DB engines in the same query, allowing for fun cross-database joins. Sign up on our homepage to learn more.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?