SQLGeeksPro

Hi There! This is going to be a quick guide for SQL Server FORMAT Function. Formatting data in SQL Server often plays a crucial role in reporting, data presentation, and user interfaces.

Here we’ll walk you through few examples of the FORMAT() function in SQL Server.

Table of Contents

1. What is the FORMAT() Function?
2. Syntax of FORMAT()
3. Formatting Dates and Times
4. Formatting Numbers
5. Specifying Culture (Locale)
6. Conclusion

1. What is the FORMAT() Function?

Introduced in SQL Server 2012, the FORMAT() function is used to return a value formatted with the specified format and culture (locale).

It is particularly useful when you need to:

• Display results in a user-friendly format
• Output locale-specific formats
• Customize currency or date formats

2. Syntax of FORMAT()

It has a very simple Syntax

FORMAT( value , format [ , culture ] )

Now let’s understand the parameters:

Value: It is the value on which you want to apply the formatting. Not to mention, it should be one of the valid data types.

Format: Specify the format in which you require the output. (for example, "MM/DD/YYYY"). It does not support Composite formatting.  This parameter should contain a valid .NET format string in the NVARCHAR data type

Culture: It is an optional parameter. By default, SQL Server uses the current session language. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement.

Culture accepts any .NET Framework supported culture as an argument or else it raises an error.

3. Formatting Dates and Times

You can use FORMAT() to format date and time values using .NET date and time format strings.

Example 1: Formatting current Date in ‘yyyy-mm-dd’ or ‘dd-mm-yyyy’ format

SELECT GETDATE() AS DefaultDateFormat
GO
SELECT FORMAT(GETDATE(),'yyyy-MM-dd') AS FormattedDate
GO
SELECT FORMAT(GETDATE(),'dd-MM-yyyy') AS FormattedDate
GO

The result set is as follows:

In this case you can observe that the default date format of GETDATE() function is “yyyy-mm-dd hh:mm:ss.mmm” and we used the FORMAT() function to display it in “yyyy-mm-dd” and “dd-mm-yyyy” format.

Example 2: Display/Extract desired format from GETDATE()

SELECT FORMAT(GETDATE(),'d') AS ShortDate
GO
SELECT FORMAT(GETDATE(),'D') AS LongDate
GO
SELECT FORMAT(GETDATE(),'m') AS MonthDate
GO
SELECT FORMAT(GETDATE(),'g') AS [MM/DD/YYYY hh:mm AM/PM]
GO
SELECT FORMAT(GETDATE(),'G') AS [MM/DD/YYYY hh:mm:ss AM/PM]
GO

You can observe the differences in result set for each parameter:

4. Formatting Numbers:

You can apply numeric format strings for currency, percentages, and custom number formats

Example1: Formatting Numbers

SELECT FORMAT(1234.56, '#,##0.00') AS [Comma Separated Formatted Number];
GO
SELECT FORMAT(1234.56, '#,##0.000') AS [Comma Separated Formatted Number With Additional Decimal Place];
GO
SELECT FORMAT(123456789, '#,#') AS [Thousand Separator];
GO
SELECT FORMAT(123456789.566, '#,#.##') AS [Thousand Separator with rounded to a specified number of decimal places]
GO
SELECT FORMAT(123456789, '##-##-#####') AS [Dash Separator];
GO

Notice the differences in output. Column names are self explanatory.

Example 2: Formatting Currency

SELECT FORMAT(1.5, 'c', 'hi-in') AS [Indian Currency / Rupee]
GO
SELECT FORMAT(1.5, 'c', 'en-US') AS [US Currency / Dollar]
GO
SELECT FORMAT(1.5, 'c', 'en-gb') AS [Great Britain Currency / Pound]
GO
SELECT FORMAT(1.5, 'c', 'de-de') AS [German Currency / Euro]
GO
SELECT FORMAT(1.5, 'c', 'ru-ru') AS  [Russian Currency / Ruble]
GO
SELECT FORMAT(1.5, 'c', 'gl-es') AS  [Spain Currency / Euro]
GO

The result set with self explanatory column names:

Example 3: Percentage Formatting

SELECT FORMAT(0.756, 'P') AS [Percentage]
GO
SELECT FORMAT(0.756, 'P0') AS [WholeNumberPercentage]
GO
SELECT FORMAT(0.756, 'P3') AS [ThreeDecimalsPercentage]
GO
SELECT FORMAT(0.756, 'P4') AS [FourDecimalsPercentage]
GO
SELECT FORMAT((47.0/48.0), '#,##.0%') AS [Percentage Rounded to One Decimal Place]
GO
SELECT FORMAT((47.0/48.0), '#,##.##%') AS [Percentage Rounded to Two Decimal Places]
GO

Result set with self explanatory column names:

5. Specifying Culture (Locale)

Now let’s see formatting dates into different languages with the culture parameter.

SELECT FORMAT(GETDATE(), 'd','hi-in') AS [Indian (hi-in) (DD-MM-YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','en-US') AS [US English (en-US) (MM/DD/YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','en-gb') AS [Great Britain English (en-gb) (DD/MM/YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','de-de') AS [German (de-de) (DD.MM.YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','zh-cn') AS [Chinese (zh-cn) (YYYY/MM/DD)]
GO 
SELECT FORMAT(GETDATE(), 'd','ru-ru') AS [Russian (ru-ru) (DD.MM.YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','gl-es') AS [Spain (gl-es) (DD/MM/YYYY)]
GO

Please note the self explanatory column names for each:

Conclusion

The FORMAT() function is an incredibly flexible tool in SQL Server, ideal for making your output more user-friendly and internationalized. While it should be used with care in terms of performance, it greatly simplifies the formatting of numbers and dates compared to older methods.

Start using FORMAT() in your reporting queries to make your data presentation cleaner and more consistent.

Hello! If you’re working with SQL Server and need to filter records based on a date range, you’re in the right place. In this article, we’ll discuss how to select data between two dates in SQL Server using best practices to ensure accuracy and performance. Date filtering is a common task when analyzing time-bound records irrespective you being SQL developer or production DBA.

Reasons to Select Data Between Two Dates:

• You want to check the successful backups happened between two dates
• What the are indexes which were part of Index maintenance in last couple of weekends
• What is the data/log file growth in a month etc.

When working with large datasets, retrieving only the relevant time-based records helps.

Microsoft SQL Server provides different methods to select data between two dates. The most common ones are as follows:

• BETWEEN Operator
• Arithmetic Operators Greater Than (>) and Less Than (<)

SELECT Data Between Two Dates in SQL Server Using BETWEEN Operator:

The BETWEEN keyword is inclusive, that means it includes both the start and end dates.

Syntax:

SELECT * FROM [TableName]  WHERE [DateColumn] 
BETWEEN 'StartDate' AND 'EndDate' --Ensure the date format matches the column data type (YYYY-MM-DD is recommended).

Now let’s take an example. Assume you have the following data in a SalesInfo table:

You want to extract the sales data between 2021-01-10 and 2025-02-28 (yyyy-mm-dd), the query would be as follows:

SELECT * FROM [SalesInfo]  WHERE [DateOfPurchase] 
BETWEEN '2021-01-10' AND '2025-02-28' --Ensure the date format matches the column data type (YYYY-MM-DD is recommended).

Pay attention to the Result set, you can see that both the dates are included.

It was pretty simple isn’t it. Now let’s see how stuff works if the date column is of type Datetime/Datetime2.

Make a note of the underlined datetime in red:

Execute the same query for ‘SalesData’ table and let’s compare the result sets:

Important to realize here is the underlined datetime value has not appeared in the result set though we said that BETWEEN operator is inclusive. Well, the catch here is when you specify the only date for a column of having data type Datetime/Datetime2 it simply considers midnight (12 am).

In other words  SQL Engine interprets ‘2021-01-10‘ as ‘2021-01-10 00:00:00.000’ and ‘2025-02-28’  as ‘2025-02-28 00:00:00.000′. Because ‘2025-02-28 21:26:54.000’ is greater than ‘2025-02-28 00:00:00.000’, it is not included. Now you know why SQL omitted the underlined date in the above example.

SELECT Data Between Two Dates using Arithmetic Operators Greater Than (>) and Less Than (<)

These operators allows the flexibility to include or exclude the start and end dates by specifying equal operator along with them. That means if you use >= and <= it will work just like BETWEEN operator. Again if you are dealing with Datetime/Datetime2 data type, the same concept of midnight applies as described above.

Let’s consider the SalesInfo table where the data type for the date column is Date.

SELECT * FROM [SalesInfo]
WHERE [DateOfPurchase] >= '2021-01-10' AND [DateOfPurchase] <= '2025-02-28'

The result set shows that both the dates are included:

As mentioned earlier it has the flexibility to exclude any of the dates. In the below example the end date is excluded by mentioning (<) operator.

SELECT * FROM [SalesInfo]
WHERE [DateOfPurchase] >= '2021-01-10' AND [DateOfPurchase] < '2025-02-28'

Conclusion:

Selecting SQL Server data between two dates is a fundamental but powerful task. Whether you’re using BETWEEN, >= AND <, the key is understanding how SQL Server handles date and time. With these techniques, you can write efficient and accurate queries to get the data you need.

You may also want to check:

How To Rename SQL Server Database

List All Database Users With Permissions

Could not drop login as the user is currently logged in

List All Permissions For A User In All Or Selective Databases

Hi there! In this article we are going to discuss how to resolve the error “The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)”

The Complete error message while rename fails for exclusive lock looks like below: Refer How To Rename SQL Server Database

Why the Error Message:

You need to have exclusive lock on the database to rename the db. Exclusive lock means there are no other database connections open/using the database. SQL Engine is smart enough not to allow database name change while other users are accessing the database.

Query to Resolve Database could not be exclusively locked:

Following query will switch the database from Multi User mode to Single User mode, then rename the database and then set it back to Multi User mode.

USE master
GO
ALTER DATABASE [DatabaseName]  --Change The Database Name
SET SINGLE_USER  WITH  ROLLBACK  IMMEDIATE
GO
ALTER DATABASE [DatabaseName] MODIFY NAME = [NewDatabaseName]
GO
ALTER DATABASE [NewDatabaseName]  --Put the new database name
SET MULTI_USER WITH ROLLBACK IMMEDIATE

Point often overlooked in the above query is the final statement, where you need to put the new database name.

To demonstrate please find the following Example for database named “SQLGeeksPro” renaming to “SQLGeeksPro_NewName”