Building Readable Dates for Reporting Dashboards in SQL Server

When you’re putting together reporting dashboards, raw datetime values like 2025-09-23 13:45:32.000 don’t do much for the average business user. People want to see “Sep 2025” or “Tuesday, September 23, 2025” rather than a timestamp that looks like it came straight from the database.

In many cases, formatting can also be handled in the reporting or application layer, which may be better for things like localization and display preferences. But there are plenty of situations where it makes sense to do this work in SQL Server itself. For example, maybe you need consistency or business-specific date logic. Fortunately, SQL Server gives us several tools for shaping dates into clear, readable labels that work well in dashboards.

Formatting Dates in SQL Server

The quickest way to transform a datetime value is with the FORMAT() function, available in SQL Server 2012 and later. It’s flexible and supports custom formats:

SELECT 
    GETDATE() AS RawDate,
    FORMAT(GETDATE(), 'D') AS ReadableDate;

Output:

RawDate                 ReadableDate
----------------------- ---------------------------
2025-09-23 01:20:45.027 Tuesday, September 23, 2025

In this example we used D as the format string. This is considered a standard date format specifier, as it automatically formats the date in a pre-determined way. In this case, the D format specifier formats the date as dddd, MMMM dd, yyyy.

We can also build our own format string using custom date format specifiers. Doing this enables us to format the date exactly how we want.

Here’s an example of how we can use custom date format specifiers to manually build the same format from the previous example:

SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy');

Output:

Tuesday, September 23, 2025

Obviously, it’s more concise to just use the D format specifier. However, the advantage that custom format specifiers have over the standard format specifiers is that we can build our own format based on our own specific needs (or the requirements of the business). We can move the various format specifiers as required. Another benefit could include the fact that custom specifiers can be more intuitive than the standard specifiers. For example, it’s easier to visualize how the date will be formatted when we see dddd, MMMM dd, yyyy than a simple D.

Alternatives to `FORMAT()`

One downside to the FORMAT() function is performance. FORMAT() can be slow when applied to large datasets, so if speed matters, you may want to stick with the older CONVERT() function:

SELECT CONVERT(varchar(12), GETDATE(), 107);

Output:

Sep 23, 2025

CONVERT() uses style codes, which aren’t as flexible but run faster. For example, 106 produces 23 Sep 2025, and 101 gives you 09/23/2025.

You can also pull out individual parts of a date with functions like DATENAME() and YEAR(). These are especially useful when you want to group data by things like month name, weekday, or year:

SELECT 
    DATENAME(month, GETDATE()) AS MonthName,
    DATENAME(weekday, GETDATE()) AS Weekday,
    YEAR(GETDATE()) AS Year,
    MONTH(GETDATE()) AS MonthNumber;

Output:

MonthName  Weekday  Year  MonthNumber
---------  -------  ----  -----------
September  Tuesday  2025  9

Labels That Work in Dashboards

Building readable dates isn’t necessarily just about formatting. It’s also about building labels that make sense when grouped in charts and tables.

If you’re building a time series chart, for example, showing “Sep 2025” is much clearer than a raw 2025-09-23.

Suppose we create and populate the following table:

-- Create the Sales table
CREATE TABLE dbo.Sales
(
    SalesId INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATE NOT NULL,
    SalesAmount DECIMAL(10,2) NOT NULL
);

-- Insert sample data
INSERT INTO dbo.Sales (OrderDate, SalesAmount)
VALUES
    ('2025-09-01', 120.50),
    ('2025-09-05', 85.00),
    ('2025-09-12', 300.00),
    ('2025-09-18', 150.25),
    ('2025-09-23', 99.99),
    ('2025-09-27', 200.00),
    ('2025-10-02', 450.00),
    ('2025-10-10', 175.75),
    ('2025-11-03', 220.00),
    ('2025-11-15', 600.00),
    ('2025-12-01', 325.50),
    ('2025-12-20', 400.00);

We can use the following query to generate monthly labels:

SELECT 
    FORMAT(OrderDate, 'MMM yyyy') AS MonthLabel,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY FORMAT(OrderDate, 'MMM yyyy')
ORDER BY MIN(OrderDate);

Result:

MonthLabel TotalSales
---------- ----------
Sep 2025   955.74
Oct 2025   625.75
Nov 2025   820.00
Dec 2025   725.50

Formatting Dates in SQL Server

Alternatives to FORMAT()

Labels That Work in Dashboards

Alternatives to `FORMAT()`