Tag Archives: FORMAT function

How to get Day or Weekday name from date in Sql Server

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

Approach 1: Using DATENAME Function

We can use DATENAME() function to get Day/Weekday name from Date in Sql Server, here we need specify datepart parameter of the DATENAME function as weekday or dw both will return the same result.

SELECT GETDATE() 'Today', DATENAME(weekday,GETDATE()) 'Day Name'
SELECT GetDate() 'Today', DATENAME(dw,GETDATE()) 'Day Name'

RESULT:

Day or weekday name from Date Sql Server 1

Approach 2: Using FORMAT Function

We can aswell use FORMAT function which is introduced in Sql Server 2012 to get Day/Weekday 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 Day/Weekday name from Date in Sql Server. But FORMAT function provides lot of options compared to DATENAME function. To know more about 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(),'dddd') 'Day Name'

RESULT:
Day or weekday name from Date Sql Server 2

How to get month name from date in 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