Feeds:
Posts
Comments

Posts Tagged ‘DATETRUNC()’

Sometimes, we come across a case where we have given one date and we need to calculate multiple dates based on this date. This can be achieved in the earlier versions of the SQL Server (earlier than 2022), but we had to use multiple functions like CONVERT(), DATEADD(), DATEDIFF() etc with lots of complexity.

Fortunately, a new function shipped in SQL Server 2022 namely DATETRUNC() which helps us to achieve this scenario easily.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this new function.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160 

Prerequisite:

In order to set the first day of the week as Monday, we need to run the below script.


SET DATEFIRST 1;
GO

First Date of Previous Week

  • Old Approach

In the old approach, we have to extract first date of the previous week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							 AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) -1, 0)) AS [First Date Of Previous Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the previous week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date Date;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
	  , DATEADD(WEEK, -1
	  , DATETRUNC(WEEK, @Date))   AS [First Date Of Previous Week];
GO
--OUTPUT

Last Date of Previous Week

  • Old Approach

In the old approach, we have to extract last date of the previous week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +0, -1)) AS [Last Date Of Previous Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the previous week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
	  , DATEADD(DAY, -1
	  , DATEADD(WEEK, 0
	  , DATETRUNC(WEEK, @Date)))  AS [Last Date Of Previous Week];
GO
--OUTPUT

First Date of Current Week

  • Old Approach

In the old approach, we have to extract first date of the current week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							 AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +0, 0)) AS [First Date Of Current Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the current week using DATETRUNC() function as shown below.

DECLARE @Date Date;
SET @Date = GETDATE();

SELECT  @Date				   AS [Current Date]
	  , DATETRUNC(WEEK, @Date) AS [First Date of Current Week];
GO
--OUTPUT

Last Date of Current Week

  • Old Approach

In the old approach, we have to extract last date of the current week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +1, -1)) AS [Last Date Of Current Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the current week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE()

SELECT  @Date					 AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(WEEK, 1
	  , DATETRUNC(WEEK, @Date))) AS [Last Date Of Current Week];
GO
--OUTPUT

First Date of Next Week

  • Old Approach

In the old approach, we have to extract first date of the next week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							 AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +1, 0)) AS [First Date Of Next Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the next week using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
	  , DATEADD(WEEK, 1
	  , DATETRUNC(WEEK, @Date))  AS [First Date Of Next Week];
GO

--OUTPUT

Last Date of Next Week

  • Old Approach

In the old approach, we have to extract last date of the next week using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(WEEK
	  , DATEDIFF(WEEK, 0, @Date) +2, -1)) AS [Last Date Of Next Week];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the next week using DATETRUNC() & DATEADD() function as shown below.


DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(WEEK, 2
	  , DATETRUNC(WEEK, @Date))) AS [Last Date Of Next Week];
GO
--OUTPUT

Conclusion:

We used DATETRUNC() function to achieve it and found it easier & simpler as compared to earlier version of SQL Server.

Read Full Post »

Sometimes, we come across a case where we have given one date and we need to calculate multiple dates based on this date. This can be achieved in the earlier versions of the SQL Server (earlier than 2022), but we had to use multiple functions like CONVERT(), DATEADD(), DATEDIFF() etc with lots of complexity.

Fortunately, a new function shipped in SQL Server 2022 namely DATETRUNC() which helps us to achieve this scenario easily.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this new function.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160 

First Date of Previous Month

  • Old Approach

In the old approach, we have to extract first date of the previous month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) -1, 0)) AS [First Date Of Previous Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the previous month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
	  , DATEADD(MONTH, -1
	  , DATETRUNC(MONTH, @Date)) AS [First Date Of Previous Month];
GO
--OUTPUT

Last Date of Previous Month

  • Old Approach

In the old approach, we have to extract last date of the previous month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							   AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +0, -1)) AS [Last Date Of Previous Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the previous month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
      , DATEADD(DAY, -1
	  , DATETRUNC(MONTH, @Date)) AS [Last Date Of Previous Month];
GO
--OUTPUT

First Date of Current Month

  • Old Approach

In the old approach, we have to extract first date of the current month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +0, 0)) AS [First Date Of Current Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the current month using DATETRUNC() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					AS [Current Date]
	  , DATETRUNC(MONTH, @Date) AS [First Date of Current Month];
GO
--OUTPUT

Last Date of Current Month

  • Old Approach

In the old approach, we have to extract last date of the current month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							   AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +1, -1)) AS [Last Date Of Current Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the current month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(MONTH, 1
	  , DATETRUNC(MONTH, @Date))) AS [Last Date Of Current Month];
GO
--OUTPUT

First Date of Next Month

  • Old Approach

In the old approach, we have to extract first date of the next month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							  AS [Current Date]
	  , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +1, 0)) AS [First Date Of Next Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the next month using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					 AS [Current Date]
	  , DATEADD(MONTH, 1
	  , DATETRUNC(MONTH, @Date)) AS [First Date of Next Month];
GO
--OUTPUT

Last Date of Next Month

  • Old Approach

In the old approach, we have to extract last date of the next month using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date							   AS [Current Date]
      , CONVERT(DATE
	  , DATEADD(MONTH
	  , DATEDIFF(MONTH, 0, @Date) +2, -1)) AS [Last Date Of Next Month];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the next month using DATETRUNC() & DATEADD() function as shown below.


DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT  @Date					  AS [Current Date]
      , DATEADD(DAY, -1
	  , DATEADD(MONTH, 2
	  , DATETRUNC(MONTH, @Date))) AS [Last Date Of Next Month];
GO
--OUTPUT

Conclusion:

We used DATETRUNC() function to achieve it and found it easier & simpler as compared to earlier version of SQL Server.

Read Full Post »

Sometimes, we come across a case where we are given one date and we need to calculate multiple dates based on this date. This could be achieved in the earlier versions of the SQL Server (earlier than 2022), but we had to use multiple functions like CONVERT(), DATEADD(), DATEDIFF() etc with lots of complexity.

Fortunately, a new function shipped in SQL Server 2022 namely DATETRUNC() which helps us to achieve this scenario easily.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this new function.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160 

First Date of Previous Quarter

  • Old Approach

In the old approach, we had to extract first date of the previous quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date							   AS [Current Date]
	 , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) -1, 0)) AS [First Date Of Previous Quarter];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the previous quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
   
SELECT @Date                      AS [Current Date]
     , DATEADD(QUARTER, -1 
     , DATETRUNC(QUARTER, @Date)) AS [First Date Of Previous Quarter]; 
GO
--OUTPUT

Last Date of Previous Quarter

  • Old Approach

In the old approach, we had to extract last date of the previous quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date								AS [Current Date]
     , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +0, -1)) AS [Last Date Of Previous Quarter];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract last date of the previous quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                      AS [Current Date]
     , DATEADD(DAY, -1
     , DATETRUNC(QUARTER, @Date)) AS [Last Date Of Previous Quarter];
GO
--OUTPUT

First Date of Current Quarter

  • Old Approach

In the old approach, we had to extract first date of the current quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE()

SELECT @Date							   AS [Current Date]
	 , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +0, 0)) AS [First Date Of Current Quarter];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the current quarter using DATETRUNC() function as shown below.

DECLARE @Date DATE;
SET	@Date = GETDATE()

SELECT @Date					 AS [Current Date]
	 , DATETRUNC(QUARTER, @Date) AS [First Date of Current Quarter];
GO
--OUTPUT

Last Date of Current Quarter

  • Old Approach

In the old approach, we had to extract last date of the current quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date								AS [Current Date]
     , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +1, -1)) AS [Last Date Of Current Quarter];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the current quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date					   AS [Current Date]
     , DATEADD(DAY, -1
	 , DATEADD(QUARTER, 1
	 , DATETRUNC(QUARTER, @Date))) AS [Last Date Of Current Quarter];
GO
--OUTPUT

First Date of Next Quarter

  • Old Approach

In the old approach, we had to extract first date of the next quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date							   AS [Current Date]
	 , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +1, 0)) AS [First Date Of Next Quarter];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the first date of the next quarter using DATETRUNC() & DATEADD() function as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();
 
SELECT @Date                      AS [Current Date]
     , DATEADD(QUARTER, 1
     , DATETRUNC(QUARTER, @Date)) AS [First Date Of Next Quarter];
GO
--OUTPUT

Last Date of Next Quarter

  • Old Approach

In the old approach, we had to extract last date of the next quarter using multiple functions (CONVERT(), DATEADD(), DATEDIFF()) as shown below.

DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date								AS [Current Date]
     , CONVERT(DATE
	 , DATEADD(QUARTER
	 , DATEDIFF(QUARTER, 0, @Date) +2, -1)) AS [Last Date Of Next Quarter];
GO
--OUTPUT

  • New Approach

In the new approach, we can extract the last date of the next quarter using DATETRUNC() & DATEADD() function as shown below.


DECLARE @Date DATE;
SET @Date = GETDATE();

SELECT @Date					   AS [Current Date]
     , DATEADD(DAY, -1
	 , DATEADD(QUARTER, 2
	 , DATETRUNC(QUARTER, @Date))) AS [Last Date Of Next Quarter];
GO
--OUTPUT

Conclusion:

We used DATETRUNC() function to achieve it and found it easier & simpler compared to earlier version of SQL Server.

Read Full Post »

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 »