How to get month name from date in Sql Server

Sql Server, , , , , , , , , , ,

Many a times we come across a scenario where we may need to get Month name from Date in Sql Server. In this article we will see how we can get Month name from Date in Sql Server.

Approach 1: Using DATENAME Function

We can use DATENAME() function to get Month name from Date in Sql Server, here we need specify datepart parameter of the DATENAME function as month or mm or m all will return the same result.

SELECT GETDATE() 'Today', DATENAME(month,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(mm,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(m,GETDATE()) 'Month Name'

RESULT:
Month name from date in sql server 1

If you need result in different language than the default one, then we need to use the SET LANGUAGE statement to change the Sql Server default language for the current session. Below example shows how we can get the Month name in Russian from Date.

--Change default langauage to Russian for this session
SET LANGUAGE Russian
SELECT DATENAME(mm, GETDATE()) 'Russian Month Name'
--Change the language back to English from Arabic
SET LANGUAGE English
SELECT DATENAME(mm, GETDATE()) 'English Month Name'

RESULT:
Month name from date in sql server 4

The sys.syslanguages catalog view provides the list of languages to which we can change the current sessions language using SET LANGUAGE statement.

Approach 2: Using FORMAT Function

We can aswell use FORMAT function which is introduced in Sql Server 2012 to get Month name from Date in Sql Server. It is not an Sql Server native function instead it is .NET CLR dependent function. I would prefer the first approach instead of this approach for getting Month name from Date in Sql Server. But FORMAT function provides lot of options compared to DATENAME function. To know more on FORMAT function you may like to go through a detailed article on FORMAT STRING FUNCTION IN SQL SERVER 2012 which I have written couple of years back.

SELECT GETDATE() 'Today', FORMAT(GETDATE(),'MMMM') 'Month Name'

RESULT
Month name from date in sql server 2

How to replace NULL value by 0 in the Dynamic Pivot result – Sql Server

Sql Server, , , , , , , , ,

For the previous articles PIVOT and UNPIVOT in Sql Server and Dynamic PIVOT in Sql Server, recieved couple of comments requesting: how to replace NULL value by 0 in the PIVOT result?. In this article let us understand replacing NULL values in the PIVOT result with an example.

PROBLEM

Below is the pictorial explanation of this problem of NULL value in the PIVOT result:

Replace Null by Zero PIVOT result Sql Server 1

ScriptBelow is the script to create the temporary table #CourseSales table with sample data as depicted in the above image:

CREATE TABLE #CourseSales
 (Course VARCHAR(50),Year INT,Earning MONEY)
 GO
 --Populate Sample records
 INSERT INTO #CourseSales VALUES('.NET',2012,10000)
 INSERT INTO #CourseSales VALUES('.NET',2012,5000)
 INSERT INTO #CourseSales VALUES('Java',2012,20000)
 INSERT INTO #CourseSales VALUES('Java',2013,30000)
 GO

Below is the Dynamic PIVOT script to generate the PIVOT result with NULL value as shown in the above image:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
 DECLARE @ColumnName AS NVARCHAR(MAX)
 --Get distinct values of the PIVOT Column
 SELECT @ColumnName= ISNULL(@ColumnName + ',','')
 + QUOTENAME(Course)
 FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
 --Prepare the PIVOT query using the dynamic
 SET @DynamicPivotQuery =
 N'SELECT Year, ' + @ColumnName + '
 FROM #CourseSales
 PIVOT(SUM(Earning)
 FOR Course IN (' + @ColumnName + ')) AS PVTTable'
 --Execute the Dynamic Pivot Query
 EXEC sp_executesql @DynamicPivotQuery
 GO

RESULT of the above query:
Replace Null by Zero PIVOT result Sql Server 2

SOLUTION

We can modify the previous query like below to replace NULL by 0 in the dynamic PIVOT query result:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
	= ISNULL(@PivotSelectColumnNames + ',','')
	+ 'ISNULL(' + QUOTENAME(Course) + ', 0) AS ' 
	+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Year, ' + @PivotSelectColumnNames + '
FROM #CourseSales
PIVOT(SUM(Earning)
FOR Course IN (' + @PivotColumnNames + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:
Replace Null value by Zero in the Dynamic PIVOT result Sql Server 3

[ALSO READ] PIVOT and UNPIVOT in Sql Server
Dynamic PIVOT in Sql Server

How to STOP or ABORT or BREAK the execution of the statements in the current batch and in the subsequent batches separated by GO Statement based on some condition in SQL SERVER

Sql Server, , , , ,

Sometime back came across a scenario where I needed to STOP or ABORT the execution of the next statements in the current batch and in the subsequent batches based on some condition. Experimented multiple approach but in most of the scenarios was able to stop the execution of the subsequent statements in the current batch, but not the statements after the go statement. Two options worked one was the RAISERROR and another was the use of the SET NOEXEC ON option. I would prefer using SET NOEXEC ON option compared to RAISERROR. This article explains the problem, failed solution approaches and the successful approaches with examples.

Let us first understand what problem I am speaking about here:

 PRINT '-----FIRST Batch - Start--------'
 IF(1=1)
 RETURN -- Intention is to stop execution
 PRINT '-----FIRST Batch - End--------'
 GO
 PRINT '-----SECOND Batch--------'
 GO
 PRINT '-----THIRD Batch--------'
 GO

The intention with which I have put the RETURN statement on line no. 3 is to stop the execution of the next PRINT statement i.e. PRINT ‘—–FIRST Batch – End——–‘ and the subsequent PRINT statements PRINT ‘—–SECOND Batch——–‘ and PRINT ‘—–THIRD Batch——–‘. Let us see what is the result:

RESULT:

STOP or ABORT the execution Sql Server 1

From the result it is clear that the RETURN statement was able to stop the execution of the PRINT statement in the current batch, but not able to stop the execution of the PRINT statements after the GO statement.

Let us see Failed attempts to solve this problem with RAISERROR/THROW

Example 1: Try to solve by raising an error using RAISERROR statement with severity level 16

 PRINT '-----FIRST Batch - Begining--------'
 IF(1=1)
 RAISERROR('RAISERROR Approach',16,1) -- Intention is to stop execution
 PRINT '-----FIRST Batch - Ending--------'
 GO
 PRINT '-----SECOND Batch--------'
 GO
 PRINT '-----THIRD Batch--------'
 GO

RESULT:

STOP or ABORT the execution Sql Server 2

Example 2: Try to solve by raising an error using THROW statement

 PRINT '-----FIRST Batch - Begining--------'
 IF(1=1)
 Throw 50000, 'THROW Approach', 1 -- Intention is to stop execution
 PRINT '-----FIRST Batch - Ending--------'
 GO
 PRINT '-----SECOND Batch--------'
 GO
 PRINT '-----THIRD Batch--------'
 GO

RESULT:

STOP or ABORT the execution Sql Server 3

From the above examples result it is clear that neither RETURN statement nor RAISERROR statement or THROW statement are able to stop the execution of the statements in the subsequent batches separated by the GO statement.

Let us see the prefered approach to solve this issue by using the SET NOEXEC ON option

 PRINT '-----FIRST Batch - Begining--------'
 IF(1=1)
 SET NOEXEC ON -- Intention is to stop execution
 PRINT '-----FIRST Batch - Ending--------'
 GO
 PRINT '-----SECOND Batch--------'
 GO
 PRINT '-----THIRD Batch--------'
 GO
 SET NOEXEC OFF

RESULT:

STOP or ABORT the execution Sql Server 4

From the result it is clear that after the execution of the SET NOEXEC ON statement at line 3, further statements are not executed.

You may be wondering why on line 10 I have written SET NOEXEC OFF. The reason for this is, the SET NOEXEC ON statement on line no. 3 instructs sql server to stop executing the statements after it in the current session. To reset this option for the current session we have to execute the SET NOEXEC OFF statement. Let us understand this with an example:

PRINT '-----FIRST Batch - Begining--------'
IF(1=1)
 SET NOEXEC ON -- Intention is to stop execution
PRINT '-----FIRST Batch - Ending--------'
GO
PRINT '-----SECOND Batch--------'	
GO
PRINT '-----THIRD Batch--------'	
GO
SET NOEXEC OFF
GO
PRINT '-----FOURTH Batch--------'	
GO

RESULT:

STOP or ABORT the execution Sql Server 5

Note the statement SET NOEXEC ON causes sql server to stop executing the subsequent statement after it, but it compiles all the statements.

RAISERROR approach to solve this problem – Not Prefered

You may be wondering at the beginning of this article I have mentioned that RAISERROR can also be used to solve this problem. But one of the examples in this article shows that even RAISERROR can’t solve this issue. RAISERROR can solve this problem if we raise an error with severity >= 20 with LOG option. But only the User with SysAdmin rights can raise error with this severity and it also terminates the connection. Because of this reason, RAISERROR is not a preferred approach for me to solve this problem.

Please let me know your prefered approach for solving this problem and also correct me if my understanding is not correct.