SQL Server: Truncating Dates

      2 Comments on SQL Server: Truncating Dates

TRUNC is a function I used a lot when I worked with Oracle and helped me slicing my data in several different ways, and keeping the columns in date format.

That function is not available in SQL Server unless you work it around by using CONVERT or DATEPART. But both change the content to VARCHAR.

Well, several years ago I found a way to workaround the issue and keep the data in DATE format (and it took me this long to post it):

1
2
-- Truncate to the current hour
SELECT DATEADD(HH, DATEDIFF(HH,0,GETDATE()), 0)
1
2
-- Truncate to the current day
SELECT DATEADD(DD, DATEDIFF(DD,0,GETDATE()), 0)
1
2
-- Truncate to the current month
SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()), 0)

As per BOL, we can use any of these datepart arguments:

  1. year (or yy, yyyy)
  2. quarter (or qq, q)
  3. month (or mm, m)
  4. dayofyear (or dy, y)
  5. day (or dd, d)
  6. week (or wk, ww)
  7. weekday (or dw, w)
  8. hour (or hh)
  9. minute (or mi, n)
  10. second (or ss, s)
  11. millisecond (or ms)
  12. microsecond (or mcs)
  13. nanosecond (or ns)

You can come up with all kinds of GROUP BYs, and filtering and keep the data in Date format at the same time.

A couple of days ago, while browsing the AskSSC community forum, a question came up: “How to get quarter start date and end date”. Because I wanted to exercise my brain a little, decided to help out and posted:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @min_date DATETIME, @max_date DATETIME;
SET @min_date = '1/1/2009';
SET @max_date = '12/31/2010';

-- Working Vars
SET @max_date = DATEADD(QUARTER, DATEDIFF(QUARTER,0,@max_date), 0) + 1;

WITH CTE_dummy AS (
    SELECT TOP(DATEDIFF(QUARTER, @min_date, @max_date) + 1) ROW_NUMBER() OVER(ORDER BY object_id) - 1 AS sequence from sys.columns
)
SELECT CASE WHEN (DATEPART(QUARTER, @min_date) + sequence) % 4 = 0 THEN 4 ELSE (DATEPART(QUARTER, @min_date) + sequence) % 4 END AS [Quarter],
    DATEADD(QUARTER, sequence, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) AS [Start_Date],
    DATEADD(QUARTER, sequence, DATEADD(QUARTER, 1, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) - 1) AS [End_Date]
FROM CTE_dummy;

For the solution I used the ROW_NUMBER() windowing function, but deals with dates truncation to tackle the core question.

Hope it helps.

Share
  • Colin M

    Fantastic. Here’s a bit extra to get the week with respect to Monday:

    DATEADD(WW, DATEDIFF(WW, 0, DATEADD(DAY, -1, GETDATE())), 0) AS [MondayWeek]