1. SQL Recipes
2. Database compatibility
3. SQL Server: Date truncation for custom time periods like year, quarter, month, etc.

SQL Server: Date truncation for custom time periods

Oracle has the trunc function and PostgreSQL / Redshift have the date_trunc function that allows you to truncate a timestamp to a specific unit of measure like year, quarter, month, week, etc. These functions are handy when rolling up and aggregating data for custom intervals.

If you want a similar function for SQL Server, you can use this macro:

select dateadd({period}, datediff({period}, 0, {field}), 0)

Where {period} is the period of interest like YEAR, QUARTER, MONTH, DAY, HOUR, etc. and {field} is the column of interest.

Examples

Silota's SQL Macro for date truncation

Silota makes this super easy with our SQL Macros. For example:

    declare @dt as datetime = '2017-05-13T01:17:42Z';

    select @dt as Original,
        'year' as Period,
        [@dt:year] as Result
    union
    select @dt as Original,
        'quarter' as Period,
        [@dt:quarter] as Result
    union
    select @dt as Original,
        'month' as Period,
        [@dt:month] as Result
    union
    select @dt as Original,
        'week' as Period,
        [@dt:week] as Result
    union
    select @dt as Original,
        'day' as Period,
        [@dt:day] as Result
    union
    select @dt as Original,
        'hour' as Period,
        [@dt:hour] as Result