Feeds:
Posts
Comments

Posts Tagged ‘CONVERT’

SQL Server 2022 – TSQL – How to get previous, current and last week dates using DATETRUNC() function

Posted in Solutions, SQL SERVER, tagged , , , , , , , , , , , on December 8, 2022| Leave a Comment »

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 »

SQL Server 2022 – TSQL – How to get previous, current and last quarter dates using DATETRUNC() function (final)

Posted in SQL SERVER, tagged , , , , , , , , , , , on December 2, 2022| Leave a Comment »

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 »

SQL Server – CONCAT() Function

Posted in Functions, SQL SERVER, tagged , , , , , on November 29, 2022| Leave a Comment »

Sometimes, we need to concatenate multiple fields to make a consolidated field. These fields may have same data type or multiple data types as well. In the past, we used multiple functions like ISNULL, CONVERT, CAST and used an Add (+) operator to concatenate it.

Now, we have a smart function called CONCAT() to achieve above mentioned scenario easily. This function is intelligent enough that to combine data of single / multiple data types. It converts all the data types to string before joining them together.

Let me show you some examples using old / new approaches.

Example 1: Concatenate multiple columns having NULL values

  • Old Approach Using Add (+) Operator

In old approach, we simply apply add(+) operator to combine multiple fields as shown below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , [Address] + ' ' + [City] + ' ' + [Region] AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

As you can see above, if one of the columns is having NULL value, which you are trying to concatenate, then result will be NULL regardless of which field has value or not.

To resolve NULL issue, we need to use ISNULL function to replace NULL values with empty space and ISNULL should be applied to all columns which we are trying to concatenate, since most of the time, we may not know which column may have NULL values, as shown below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , ISNULL([Address],'') + ' ' + ISNULL([City],'') + ' ' + ISNULL([Region],'') AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

  • New Approach Using CONCAT() Function :

Now, we have seen in earlier approach that how much effort required to concatenate fields. In the new approach, we just need to use a single function which is called CONCAT() function. This function allows us to concatenate multiple columns (varchar, int, datetime etc.) easily as shown in the example below.

USE Northwind
GO

SELECT [CompanyName]
     , [Address]
	 , [City]
	 , [Region]
	 , CONCAT([Address],' ',[City],' ',[Region]) AS [FullAddress]
FROM [dbo].[Suppliers];
GO
--OUTPUT

Example 2: Concatenate multiple columns having different data types

  • Old Approach Using Add (+) Operator

Let’s concatenate multiple data types column using Add (+) operator as shown below but it generates the error since you cannot combine different data types of columns with Add (+) operator, how we were handling such cases earlier.

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , [SupplierID] + ' ' + [ContactName] AS [SupplierID&Name]
FROM  [dbo].[Suppliers];
GO
--OUTPUT

In order to resolve this error, we need to use the CONVERT() function which will convert the int value to nvarchar datatype to make it same data type (string) for both columns and then concatenate it as shown below:

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , CONVERT(NVARCHAR(50)
	 , [SupplierID]) + ' ' + [ContactName] AS [SupplierID&Name]
FROM   [dbo].[Suppliers];
GO
--OUTPUT

  • New Approach Using CONCAT() function:

As we can see many hurdles and complexity in the old approach, in new approcah we need to simply apply CONCAT() function where it itself converts all columns data type to string and then concatenate it and retrun string as an output, as described in below example.

USE Northwind
GO

SELECT [SupplierID] 
     , [ContactName]
	 , CONCAT([SupplierID],' ',[ContactName]) AS [SupplierId&Name]
FROM   [dbo].[Suppliers];
GO
--OUTPUT

Conclusion:

I used CONCAT() function multiple times and found out extremly useful since it automatically handles NULL cases, different data type cases etc. If you use this function, do let me know your experience.

Read Full Post »

SQL SERVER – Multiple ways to convert Datetime to Varchar

Posted in Solutions, SQL SERVER, tagged , , , , , , on February 4, 2014| Leave a Comment »

Data presentation is one of the aspects that is much more important when you need to present a report to end user. Usually, I recommend that you should do it at the presentation layer (front end). However, sometimes we need to do all the formatting at database level itself and just present it at presentation level. One of the most frequently formattings we usually come across is datetime data type formatting and to format datetime we need to convert it into varchar data type.

Given below are the solutions.

Solution 1 :
In this solution, we need to use the Traditional method using CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, CONVERT(VARCHAR(11),OrderDate,113)
AS [OrderDate Using Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.1

Solution 2 :
In this solution, we need to use one of the new conversion functions shipped with SQL Server 2012 namely TRY_CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, TRY_CONVERT(VARCHAR(11),OrderDate,113)
AS [OrderDate Using Try_Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.2

Solution 3 :
In this solution, we need to use one of the new formatting functions shipped with SQL Server 2012 namely FORMAT function.
I usually recommend this method because it gives you variety for formatting.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, FORMAT(OrderDate,'dd MMM yyyy')
AS [OrderDate Using Format]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.3

Read Full Post »

SQL SERVER – How to remove leading zeros after a decimal point

Posted in Solutions, SQL SERVER, tagged , , , , , , on January 20, 2014| 2 Comments »

Recently, I came across a query in a blog, ‘How to remove leading zeros after a decimal point’. I then started developing the shortest possible solution and thought of sharing with my blog readers. Let me create a sample to demonstrate the solution.

DECLARE @tbl_sample AS TABLE
(
[ID] INT,
[Col_Varchar] VARCHAR(50)
)

INSERT INTO @tbl_sample VALUES (1,'2013.0000000001')
INSERT INTO @tbl_sample VALUES (2,'2014.0000000002')
INSERT INTO @tbl_sample VALUES (3,'2015.0000000003')
INSERT INTO @tbl_sample VALUES (4,'2016.0000000044')
INSERT INTO @tbl_sample VALUES (5,'2017.0000000555')

SELECT * FROM @tbl_sample
--OUTPUT

remove leading zero.1.1

SOLUTION 1 :
Given below is the solution using PARSENAME & CONCAT function.

--This script is compatible with SQL Server 2012 and above.
SELECT [ID]
,CONCAT(PARSENAME([Col_Varchar],2)
,'.',CONVERT(INT,PARSENAME([Col_Varchar],1)))
AS [Col_Varchar]
FROM @tbl_sample
--OUTPUT

remove leading zero.1.2

SOLUTION 2 :
Given below is the solution using PARSENAME & CONVERT function.

--This script is compatible with SQL Server 2005 and above.
SELECT
[ID]
,CONVERT(VARCHAR(5),PARSENAME([Col_Varchar],2))
+ '.'
+ CONVERT(VARCHAR(5),CONVERT(INT,PARSENAME([Col_Varchar],1)))
AS [Col_Varchar]
FROM @tbl_sample
--OUTPUT

remove leading zero.1.3

Read Full Post »