When using SQL Server, you have a few different options when you need to return the month name from a date using T-SQL. By month name, I’m not talking about the month number (such as 07). I’m talking about the full name of the month (such as July).
For example, when given a date of 2018-07-01, you want July to be returned.
This article presents three ways to return the month name from a date in SQL Server using T-SQL.
Each time you create a new login in SQL Server, you have the choice of assigning a default language to that login. If you don’t do this, the login will use the default language as specified in the default language server configuration option.
This article demonstrates how to set the default language server configuration option in SQL Server, using T-SQL.
When you connect to SQL Server, usually the date format is determined by your language. The default language for a session is the language for that session’s login, unless overridden on a per-session basis by using the Open Database Connectivity (ODBC) or OLE DB APIs.
The date format setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.
Despite the fact that the your session’s language determines the date format, you can override the date format if required. For example, if your language is us_english, the date format will be mdy (so that 07/01/2018 represents the 1st of July and not the 7th of January). You can change this so that the date format is dmy (or any other format) while the language remains us_english.
You can use T-SQL to explicitly set the date format of the current session by using the SET DATEFORMAT statement.
When using SQL Server, your current connection includes a number of options that determine things like the language, date formats, etc. These could be set at whatever the default is, but they can also be overridden during the session by using a SET statement.
The date format affects the interpretation of character strings as they are converted to date values for storage in the database. When the language is set using SET LANGUAGE, the date format setting is implicitly set accordingly. This can be explicitly overridden with the SET DATEFORMAT statement.
In any case, you can find the current date format by using the DBCC USEROPTIONS command. This command returns the SET options that have been set for the current connection.
In SQL Server, you can find out the default language for a given user by querying the sys.server_principals system catalog view.
This view contains a row for every server-level principal. It contains information such as the principal’s name, type, create/modify date, default database, default language, etc. A principal is an entity that can request SQL Server resources.
When working with dates in SQL Server, it’s easy to get tripped up with different date formats. For example, someone from the US might consider 01/07/2018 to mean the 7th of January, but someone from the UK might consider it to mean the 1st of July.
In many cases you might not even know which date format is used for any particular language/culture. Fortunately, SQL Server stores this information in its resource database, and you can retrieve it by using either of the two methods below.
The following is a list of all languages installed with the SQL Server 2017 database engine. This includes the language, along with its associated date formats.
This list can be returned by executing the sp_helplanguage stored procedure, or by querying the sys.syslanguages view directly. The list below contains the column names and their corresponding values for each language.
When a new login is created in SQL Server, it is assigned a default language. This language is used for system messages and date/time formats. This language will be used as the default language whenever that login connects to SQL Server (but it can also be changed to a different language within the session).
If you ever wish to find out the language that’s assigned to the current session, you can run one of the options on this page.
In SQL Server, you can set the language to be used for the current session. The current language setting determines the language used on all system messages, as well as the date/time formats to use.
This article demonstrates how to use T-SQL to set the current language environment in SQL Server.
Perhaps you’ve encountered the T-SQL PARSE(), CAST(), and CONVERT() functions when working with SQL Server and wondered what the difference is. All three functions seem to do the same thing, but there are subtle differences between them.
In this article I aim to outline the main differences between these functions.