This article provides a reference for the custom date and time format specifiers that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples.
You can combine any of these format specifiers to produce a customized format string. See below for a code example and an explanation on how custom format strings work.
This article provides a list of the standard date and time format strings that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples using different cultures and data types.
These are the standard date and time format specifiers that are supported by the .NET Framework, and therefore, SQL Server. Each standard date and time format string is an alias for a custom date and time format string.
In SQL Server, you can use the FORMAT() function to format date/time and number values as strings. In particular, the function provides “locale-aware” formatting, and the function accepts a “culture” argument, which allows you to specify a culture or language to use for the actual format. For example, you can pass en-us to ensure the results are formatted in US English format.
The culture argument is optional, so if you don’t provide it, the output will be determined by the language of the current session.
The FORMAT() function accepts any culture supported by the .NET Framework as an argument (you’re not limited to the languages explicitly supported by SQL Server).
One of the cultures supported by the .NET Framework is the invariant culture. The invariant culture is culture-insensitive. More specifically, this culture is associated with the English language but not with any country/region.
To specify that FORMAT() should output the results using the invariant culture, simply use "iv" for the culture argument (the third argument).
In SQL Server, when you use the T-SQL FORMAT() function to format a time data type, you need to remember to escape any colons or periods in your format string.
This is because the FORMAT() function relies upon CLR formatting rules, which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.
In SQL Server, you can use the T-SQL FORMAT() function to format a number as a currency.
The FORMAT() function allows you to format numbers, dates, currencies, etc. It accepts three arguments; the number, the format, and an optional “culture” argument. This article specifically deals with using the format argument to specify a currency.
Transact-SQL (T-SQL) includes a number of scalar functions that allow us to perform operation on strings when working with SQL Server. These functions accept an input string, and return either a string or a numeric value.
The following is a list of T-SQL string functions available in SQL Server. Click on each function or operator name to see an explanation of the function, its syntax, and examples.
Continue readingIn SQL Server, the T-SQL TRANSLATE() function returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.
It’s similar to the REPLACE() function, but with some important differences.
The TRANSLATE() function requires 3 arguments; the input string, the characters to be replaced, and the characters to replace them.
In SQL Server, the T-SQL STRING_ESCAPE() function escapes special characters in texts and returns the text with escaped characters.
You provide the text and the escape type as arguments when calling the function.
In SQL Server, the T-SQL STR() function returns character data converted from numeric data. The return value has a return type of varchar.
You provide the numeric data as an argument when calling the function. The function then converts it to character data. There are also two optional arguments that you can use to specify the length, and the number of places to the right of the decimal point.
In SQL Server, you can use the T-SQL SPACE() function to generate a specific number of spaces.
This can be handy for adding spaces within a string, for example, when concatenating two or more strings.
The way it works is, you provide the number of spaces you need, and it will return a string of exactly that number of spaces.