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”

In this article we are going to discuss how to fix incorrect Mexico Time in SQL Server sys.time_zone_info system catalog.

Problem:

One of our customer informed that Mexico changed their DST . In UAT, application is showing correct time which is UTC -6 however in Production the mexico time zone is showing as UTC – 5. The application is referring the sys.time_zone_info system catalog in SQL Server.

Troubleshooting:

In the first place you should understand what is the actual problem. In other words what the customer is talking about. The problem statement it is not very clear if it is SQL Server issue or OS issue or it is something to do with application code.

After a short call with the customer we got to know that as per Mexican Government order published in October 26th, 2022, that Daylight Saving Time (DST) in the United Mexican States will not be observed in 2023 calendar year.

Second is to compare the UAT and Production environment.

• No change in code at application end.
• There is no difference in OS time zone settings in application and database server.
• The result of Mexico Time Zone in sys.time_zone_info were different.

Query to check the Mexico Time Zone information:

SELECT * FROM sys.time_zone_info
WHERE name LIKE '%Mexico%'

Result:

The above result was from UAT where the output is showing correct. Likewise we checked it in Production environment and result is as follows:

Certainly we could see the difference as mentioned by the customer. What next?

• Checked the SQL Server patch and both UAT and Production Instance were in the same patch level
• Compared the OS patch level on both the servers and we could find the difference in patches installed

Being DBA you need to know how to verify these stuffs to be able to quickly identify the issue. Below screen shot will help you with the path:

The highlighted patch was not installed in Prod because it was scheduled for next month patching cycle.

A quick read about the patch in the KB article  KB5023702 clarifies the issue:

Resolution & Conclusion:

The resolution is a no brainer once you find the issue. You have to follow the process as per your organization and get the patch installed in the server.

To conclude incorrect time zone in sys.time_zone_info is not a SQL Server issue. However being DBA you should be able to troubleshoot, find the root cause and suggest the solution. If customer complains about one server shows correct time zone while the other is incorrect without doing any changes in the application end, you should perform a thorough check at server end. The issue occurred because of not having latest OS patch and you can resolve it by going through the relevant article of the OS patch and get it installed.

Today we are going to discuss about how to resolve the “Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped”

Introduction

If you are a SQL Server DBA, you may encounter the Error 15141 when trying to delete a login id. First of all let me show you how the error message looks like in SSMS when you try to delete/drop a login.

In the above example screen shot we are trying to delete the login id ‘Admin’. I have observed that some DBA(s) do not read the error message carefully and starts beating about the bush.

There are very similar error messages while dropping logins as you can refer the following links:

Error 15141: The Server Principal Owns One or More Availability Groups

Error 15141: The Server Principal Owns One or More Endpoints

Error 15434: Could not drop login as the user is currently logged in

To emphasize if you observe the above error message clearly reads that the principal (or login) owns one or more server roles, which prevents you from dropping it.

Cause Of the Error 15141: The Server Principal Owns One or More Server Roles

When a server principal or login owns a server role, you cannot drop the login unless you first transfer ownership of the server role to another login. As a matter of fact SQL Server never allows you to drop a login if it owns any object. Hence it throws the error preventing you to drop the server principal or login.

Resolution

To resolve the error, you first need to identify the Server Roles owned by the login or server principal. Then you need to transfer the ownership for each of the server roles to ‘sa’ or any other login as per your organization standard.

1. Query to Identify the Server Roles the Login owns

SELECT sp1.name AS ServerRoleName, 
       sp2.name AS RoleOwnerName
       FROM sys.server_principals AS sp1
       JOIN sys.server_principals As sp2
       ON sp1.owning_principal_id=sp2.principal_id
       WHERE sp2.name='Admin' --Change the login name

Sample Output:

Here in the above example it shows that the login id ‘Admin’ owns two Server roles. On the contrary if the login would have own one or more database role(s), it would allow to delete the login but not the user. Now we’ll change the ownership.

2. Query to Change the Server Role Owner:

USE [master]
GO
ALTER AUTHORIZATION ON SERVER ROLE :: [ServerRole-Test] TO [sa] --Change The ServerRole Name and login Name
GO
ALTER AUTHORIZATION ON SERVER ROLE :: [AnotherServerRole-Test] TO [sa] --Change The ServerRole Name and login Name
GO

3. Drop the Login:

USE [master]
GO
DROP Login [Admin] --change the login Name

Query Result:

As can be seen now the the drop command completed successfully.

Conclusion:

To summarize the error Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped occurs when the login or server principal owns one or more Server Roles and you are trying to drop the login. Obviously you should not take any knee jerk reaction. After all you are trying to resolve the issue. Hence you should first list out the Server Roles which the login owns. Then change the ownership to [sa] or any other login as per your organization standards. Finally delete or drop the login. Definitely this should resolve the issue. Important to realize that this method will allow you to delete the login even if the login owns and database roles. Hence to repeat you need to be very careful while working in production systems.