t-sql

How to Find the Date Format Being Used in the Current Session in SQL Server (T-SQL)

by

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.

Read more

How to Find a User’s Default Language in SQL Server (T-SQL)

by

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.

Read more

How to Find the Date Formats Used for a Specific Language in SQL Server (T-SQL)

by

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.

Read more

3 Ways to Get the Language of the Current Session in SQL Server (T-SQL)

by

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.

Read more

PARSE() vs CAST() vs CONVERT() in SQL Server: What’s the Difference?

by

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.

Read more

PARSE() vs TRY_PARSE() in SQL Server: What’s the Difference?

by

In SQL Server, the PARSE() and TRY_PARSE() functions are used for translating a value into another data type. They essentially do the same thing, with one exception; how they deal with errors.

If PARSE() fails when attempting to parsing to a different data type, it will return an error. If TRY_PARSE() fails, it will return NULL.

Read more

6 Ways to Convert a String to a Date/Time Value in SQL Server

by

If you need to convert a string into a date/time value in SQL Server, you have a number of options. In this post I outline six T-SQL functions that allow you to do this.

The six functions are:

  • CAST()
  • CONVERT()
  • PARSE()
  • TRY_CAST()
  • TRY_CONVERT()
  • TRY_PARSE()

Below are example of how you can use these functions to convert a string to a date/time data type.

Read more

How to Convert a String to a Date/Time in SQL Server using PARSE()

by

If you work with SQL Server, chances are you’ve used at least one of the CONVERT() or CAST() functions to convert from one data type to another. If you’ve ever encountered an error while trying to convert a string to a date/time data type, the PARSE() function could be what you need.

For example, if you have a string like say, Fri, 20 Jul 2018, the CONVERT() or CAST() functions will throw an error. But the PARSE() function will handle it without a problem.

The PARSE() function returns the result of an expression, translated to the requested data type in SQL Server. So you can use it to “translate” your string value into a date/time data type (such as date, datetime, datetime2, etc).

Read more

How to Convert a Date/Time Value to a String in SQL Server using CONVERT()

by

When using SQL Server, you can convert a date/time value into a string by using the CONVERT() function. This function allows you to convert between different data types.

In this article, we’ll be converting between various date/time data types to a varchar or nvarchar string.

One of the good things about this function is that it allows you to specify the style that the date will be returned in. For example, you can specify whether it’s returned as mm/dd/yyyy, yyyy.mm.dd, Mon dd, yyyy, etc. You can also specify whether the time component is returned and how it’s styled.

Read more