
<h2><strong>Using Date Parts</strong></h2>



<p>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:</p>



<pre class="wp-block-code"><code>select date_trunc('week', created_at), count(1) from...</code></pre>



<p>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.<br></p>



<h2><strong>Seconds, Minutes, Hours: Convert To String and Truncate</strong></h2>



<p>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:</p>



<h4><strong>Second</strong></h4>



<pre class="wp-block-code"><code>select convert(
  datetime, 
  convert(varchar(19), created_at, 120)
) 
from...</code></pre>



<h4><strong>Minute</strong></h4>



<pre class="wp-block-code"><code>select convert(
  datetime, 
  convert(varchar(16), created_at, 120) + ':00'
) 
from...</code></pre>



<h4><strong>Hour</strong></h4>



<pre class="wp-block-code"><code>select convert(
  datetime, 
  convert(varchar(13), created_at, 120) + ':00:00'
) 
from...</code></pre>



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



<p>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!</p>



<h3><strong>Days: Convert to Date</strong></h3>



<p>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:</p>



<pre class="wp-block-code"><code>select convert(date, created_at) from...</code></pre>



<p>Here’s what’s happening:</p>



<ul><li>datepart(dw, created_at) is the day of week of our created_at</li><li>dateadd(dd, 1 &#8211; datepart(dw, created_at)) subtracts enough days from created_at to pull it back to the first day of the week.</li></ul>



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



<h2><strong>Months &amp; Years: Convert To String, Truncate, Convert To Date</strong></h2>



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



<h4><strong>Month</strong></h4>



<pre class="wp-block-code"><code>select convert(
  date, 
  convert(varchar(7), created_at, 120) + '-01'
)
from...</code></pre>



<h4>Year</h4>



<pre class="wp-block-code"><code>select convert(
  date, 
  convert(varchar(4), created_at, 120) + '-01-01'
)
from...</code></pre>



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



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


Date Parts in SQL Server

curve-image-unique-image-unique