SQL Server Portal

At times we come across some cases where we need to extract date and time for a certain period of time (year, month, day, hour, minutes, seconds etc.). In earlier version of SQL Server (earlier than 2022), we use multiple functions like DATEADD, DATEDIFF etc. to achieve it.

In SQL Server 2022, a new function namely DATETRUNC() shipped which solved this problem. Now, we can just use DATETRUNC() function to extract date and time till any date and time parts.

In the given below diagram, you can see the different date & time parts which can be easily extracted by DATETRUNC() function.

Example:

Let me demonstrate the functionality of DATETRUNC() function by giving below example.

DECLARE @DateTime DATETIME2
SET @DateTime = '2022-10-27 12:02:31.9033333';
SELECT @DateTime                    AS [Current Date]
      , DATETRUNC(YEAR,@DateTime)   AS [DateTrunc Function]
      ,'Extract Date Till The YEAR' AS [Description]
UNION
SELECT @DateTime                      AS [Current Date]
     , DATETRUNC(QUARTER,@DateTime)   AS [DateTrunc Function]
     ,'Extract Date Till The QUARTER' AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(MONTH,@DateTime)   AS [DateTrunc Function]
     ,'Extract Date Till The MONTH' AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(WEEK,@DateTime)    AS [DateTrunc Function]
     ,'Extract Date Till The WEEK'  AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(DAY,@DateTime)     AS [DateTrunc Function]
     , 'Extract Date Till The DAY'  AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(HOUR,@DateTime)    AS [DateTrunc  Function]
     , 'Extract Date Till The HOUR' AS [Description]
UNION
SELECT @DateTime                      AS [Current Date]
      , DATETRUNC(MINUTE,@DateTime)   AS [DateTrunc  Function]
      , 'Extract Date Till The MINUTE'AS [Description]
UNION 
SELECT @DateTime                       AS [Current Date]
      , DATETRUNC(SECOND,@DateTime)    AS [DateTrunc  Function]
      , 'Extract Date Till The SECOND' AS [Description]
UNION
SELECT @DateTime                            AS [Current Date]
      , DATETRUNC(MILLISECOND,@DateTime)    AS [DateTrunc  Function]
      , 'Extract Date Till The MILLISECOND' AS [Description]
UNION
SELECT @DateTime                            AS [Current Date]
      , DATETRUNC(MICROSECOND,@DateTime)    AS [DateTrunc  Function]
      , 'Extract Date Till The MICROSECOND' AS [Description];
GO
--OUTPUT

Conclusion:

I found the DATETRUNC() function very useful; earlier we used multiple functions to extract date and time till whichever time period we needed, but now we can achieve it with the help of this function.

Read Full Post »